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.