Shared lock- Effecting Select query’s performance

A shared log in SQL query is applied by default by SELECT statements. The difference between Shared and exclusive locks is that exclusive lock will stop any update on the data being locked, whereas shared lock suggests the select query to wait if some update or insert operation is in progress for the row.

Shared lock will not stop any other request from selecting or updating the records.

For example- select * from employee;

The above statement will fetch employee data from employee table, but if at the same time some insert or update query is executing, it will impact the performance of select query as it has to wait for other operation (on the row/ page it is trying to read) to finish.

Workaround: If we are sure that other operations executing at the same time are not going to impact my select query, i.e. I am ok to read a few old records/ dirty read, Sql provides us keyword NOLOCK

select * from employee with (NOLOCK);

This will make sure our select query does not get impacted by insert and updates running at the same time, though we might have problem of dirty read.

Workaround 2: With NOLOCK we know we can have a dirty read problem, to avoid that, we can use READPAST. This will make the select query skip any rows being updated at the query time and hence avoids dirty reads, So we are achieving our goal of not waiting for insert/ update operations to be over plus. On the downside, the resultset will miss some of the records.

select * from employee with (READPAST);

Leave a Reply