Sql deadlock with nolock1/10/2024 ![]() Next we should look at the execution plan for the UPDATE statement: update posts set = getdate() where id = 2 The key here is order we are looking up data in the two indexes. The execution plan for the SELECT statement reveals that we will first look up some records in the idx_date_changed index and then look up more data in the clustered index and join it up. Transaction (Process ID 57) was deadlocked on lock resources withĪnother process and has been chosen as the deadlock victim.įirst lets look at the execution plans: select * from posts where = 1 Look at your your messages on the SELECT loop, you should see something like the following: Msg 1205, Level 13, State 51, Line 7 Execute the second query a few times (it may be once, may be 20 times). use a temp table to avoid filling the query analyzer window with results Open two Query Analyzer windows, in the first type: set nocount on ![]() Insert posts values ('post contents', getdate()) Insert posts values ('post contents', 1, getdate()) Run the following code snippet in Query Analyzer: create table posts (id int identity primary key, varchar(7000), int, date_changed datetime)Ĭreate index idx_date_changed on posts (, date_changed) If you can’t believe this is possible here is a demo: MSSql figures out that we have a deadlock, kills the SELECT statement and raises an error message.Connection 2 : SELECT statement tries to acquire a shared lock on index #1, since someone else is already holding an exclusive lock, it start waiting.Connection 1 : UPDATE statement attempts to acquire an exclusive lock on page #1, since someone else is already holding a shared lock, it starts waiting.Connection 2 : SELECT statement acquires a shared lock on page #1 in the database.Connection 1 : UPDATE statement acquire an exclusive lock on a key on index #1.In such cases MSSql determines the order of locks it needs to acquire and acquires them in a what appears to be linear fashion.Īnd this is where all the trouble starts. For example, it may need a shared lock on an index AND a lock on a page in the database to return results. Most of the time MSSql needs to acquire more than one lock to proceed with a portion of the SELECT statement. These locks may or may not be held for entire duration of the SELECT statement depending on various circumstances. ![]() ![]() These resources may be pages in the database where the data is stored or keys in indexes and so forth. SELECT statements acquire shared locks on various resources. The default isolation level for all SELECT statements is READ COMMITTED. The theoretical explanation is this: regardless of explicit transactions SQL Server tries to stay Atomic, Consistent, Isolated and Durable. Why SELECT statements sometimes deadlock against UPDATE statements I think that in practice it is very dangerous. I think this is almost always the wrong conclusion. In this article Jeff concluded that the WITH (NOLOCK) hint is a practical solution to many deadlocking conundrums which though, in theory, is very dangerous, in practice, solves his problem. I was reading this article about some recent deadlocking issues Jeff Atwood was having with the brand new Stack Overflow site. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |