question

Slick84 avatar image
Slick84 asked

Can a SELECT hold an exclusive lock on a table?

If I run a simple SELECT * FROM tbl, could it possibly block any other SELECTS, UPDATES, INSERTS and/or DELETES into the table I'm SELECTING from? From my knowledge, I dont think it blocks or holds exclusive locks. But I have seen a behavior where an update had been blocked while SELECTING. I know I can use the NOLOCK hint or the READ UNCOMMITED transaction isolation level. Thanks.
tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
In general a SELECT will obtain a shared lock. This will prevent data modification statements like UPDATEs and INSERTs and DELETEs, as they will require exclusive locks, so as to not change data that might be being otherwise changed or read. Another SELECT will also require a shared lock, and will obtain it, hence the name *shared*. You are right though, that you can affect the way locks are used by changing the isolation levels. Using NOLOCK, is not a silver bullet. The database locking mechanism is extremely complex and efficient, there is often little cause to manage it yourself.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 avatar image Slick84 commented ·
Nice comment. I forgot about Shared Locks. Makes more sense. Can you dive more into the difference between NOLOCK and READ UNCOMMITED TRANSACTION ISOLATION LEVEL? Thanks.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@Slick84: they are essentially the same thing, except NOLOCK is used as a table hint, whereas the other sets the locking for the connection. In other words the scope of the isolation. They both result in 'dirty reads'
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@Kev: your answer was more than enough but i started typing and took me a while before I submit mine
0 Likes 0 ·
DaniSQL avatar image
DaniSQL answered
SELECT doesn't hold exclusive lock(X) on pages rather it sets sets shared lock(S) on the pages to read and other transactions can't modify the data while shared locks exist(but can read the data by placing another shared lock). So it is expected that your SELECT blocks any updates. With Exclusive Locks(X) no other transaction can read or modify before the transaction with the exclusive lock ends. [Understanding Locking in SQL Server][2] [Locking in Microsoft SQL Server][4] (contains similar example with your case) [1]: http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm [2]: http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx [3]: http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm [4]: http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
@DaniSQL : the locks don't necessarily happen at the page level, they could be row, key, table, page, extent, file and even database!
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Kev, Thanks for the clarification.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.