Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Jan 7, 2008

Changing the Locking Scheme on a Table

Using the right type of locking on tables is very important to ensure application scalability. Based on the data access pattern, you'd select either Data Page lock or Data Row lock. However, if actual data usage differs from the anticipated initial analysis then there could be scalability issues. This would require changing the locking scheme on the table.

For example, suppose you have a CutomerBasket table, which was created with Data Page locking, is causing a data access bottle neck. The following command will change it to Row level locking, which increases the scalability (remember that this command can be used even if table has data):


alter table CustomerBasket lock datarows
In order to execute this command, the Into/Bulk insert option on the database needs to be temporarily Turned-ON by the DBA. Usually, this option is OFF in production since its low-logging operation.

Note: This example is based on Sybase's database. Actual details may differ based on the RDBMS being used.

Navin Varma
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap