If a row in a table (without primary key) is locked when some modification(update query) is taking place for that row, I assume that the intent locks are first acquired on table, then page before the exclusive lock is acquired on the row.
Now let's say that some other thread wishes to make modification(update query) for some other row in the same table at the very same time, then SQL Server throws the following error:
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Now is this error might be the reason that the other row of the same table was amongst the data locked in the same data page of the first query?
I know data page also selects additional data that we do not request for. So if we have a primary key in a table, then will Data Page still select additional data or only that row with the primary key?
-
It's hard to say what is happening in your scenario without knowing everything that each SPID did leading up to the deadlock. Would a [deadlock example](http://stackoverflow.com/questions/22825147/how-to-simulate-deadlock-on-sql-server/22825825#22825825) help you? – Dave Mason Jul 15 '14 at 20:16
-
The dead-lock example highlights update being done on the same row. Mine is happening on different rows. To my amazement, I made a little change to the design of the table by adding primary keys (earlier there were none) and the error 1205 is GONE. I simply wish to know why this error went away when I did this change and therefore I've wish to know is my understanding in the second question correct? – mandar Jul 15 '14 at 20:28
-
That sort of makes sense. Without the PK (or a unique index), SQL may acquire something greater than a row lock to perform an update. I couldn't find any references, but I believe in the absence of a PK (or unique index) SQL creates an underlying mechanism to uniquely identify each row (pageid concatenated with something else). But that may not be enough to prevent page locks, table locks, etc. – Dave Mason Jul 15 '14 at 20:40
4 Answers
There are multiple kinds of locks in SQL Server, for different "granularities" of data.
In a perfect world, if you only update one row, indeed it could only hold a row lock and any other row would not be locked.
The engine reserves the right to "escalate" a lock to a larger set, such as a page or even a whole table.
This can be necessary if you're updating a range.
If you're saying you have a primary key that is also, perhaps, your clustered index key. AND it is the criteria for finding a row to update... sure, you might always avoid locking too many rows.
UNLESS you have any other indexes defined on the table.
Then you still might have impact across storage units that might lead to a deadlock.
-
To understand why you saw a change, remember that in SQL Server, tables are just logical entities, and indexes are the "real" tables.
If you have no indexes at all, the db engine may indeed have no choice but to lock the whole table all the time because it has no basis for locking only a range of rows.
It needs a relevant index in order to create the lock - and spare the other rows.
Once you can say you want to update keys, for example, "1 through 5", then the db engine can define locks on those key values. And then the rest of the table is not locked!
- 1,382
- 11
- 23
-
Note that there are `PAGE` and `EXTENT` locks ([among others](http://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx#Lock_Engine)). If a table is large enough, these types of locks would not lock the entire table, providing SQL with some "choice". – Dave Mason Jul 15 '14 at 20:45
-
2*If you have no indexes at all, the db engine may indeed have no choice but to lock the whole table* this is incorrect, heap rows can be locked individually using the RID. – Remus Rusanu Jul 15 '14 at 21:19
- might be the reason that the other row of the same table was amongst the data locked in the same data page of the first query?
No. If both queries use the same granularity (eg. row) then they will obtain compatible intent locks at the high levels (table(partition), page) and different locks at the low (row) level. Even if they use incompatible granularities, no deadlock will occur. Blocking may occur, but no deadlock.
I know data page also selects additional data that we do not request for. So if we have a primary key in a table, then will Data Page still select additional data or only that row with the primary key?
This just doesn't make any sense. You are mixing logical (primary key) with physical (data page) and the data page also selects additional data that we do not request for is quite literally impossible to parse for me. What I can only speculate you're trying to say is the following:
In a table organized as a heap (no clustered index) all scans have to inspect every row to test a predicate. This will result in lock conflicts.
When concurrent updates occur on a heap (a table without a clustered index) if there are no nonclustered indexes to consider then no updates can deadlock. All updates will scan the table in the same order (the heap physical allocation order) and all locks will be acquired in the same order. Updates can block, but not deadlock.
When concurrent updates occur on a heap or a table organized as a clustered index, but there are non-clustered indexes then each update can use one (or more) non-clustered index to locate the candidate rows for the update. Updates that do not use the same predicate (ie. different WHERE clauses) can use different NC indexes, in different order. These can deadlock, as the order of acquiring the locks will differ.
When concurrent updates occur on a table organized as a clustered index then deadlocks can occur because the application can requests updates explicitly in an order that results in deadlock (Tran 1 updates keys A then B while Tran 2 updates keys B then A).
There are many more ways deadlocks can occur, but these are the basic ways UPDATE vs UPDATE deadlocks can occur.
If you want an answer about a deadlock, any deadlock, in SQL Server always start by capturing the deadlock graph. Without the deadlock info everything is speculation.
PS. Please don't refer to clustered organized tables as 'primary key'. Primary key is a logical concept.
- 288,378
- 40
- 442
- 569
This is not an easy thing to answer as multiple things can lead to deadlocks and they can be different at different times. The best way to investigate, imo, is to use SQL Server Profile and look for Deadlock Events and capture Deadlock Graphs from MSDN. This article has full details on how SQL Server does locking as well as some additional information on deadlocks and deadlock avoidance.
To answer your questions:
- Yes, both rows could be deadlocked waiting on each other (conversion deadlock)
- Depends on what your queries are asking for. Each query specifies the columns it wants, so that is in your control. If you ask for only the PK, that's what you will get.
- 425
- 2
- 3
-
Conversion deadlock can occur if they both are waiting on the same resource. The threads are updating different resources as mentioned. Regarding the second one, do you mean that even Data Page might give the row with the primary key and not any other additional rows? – mandar Jul 15 '14 at 21:00
-
Not clear on what you are asking. The structure of the data pages differ depending on the type of index(es) you have and what type of data you have. – Earl G Elliott III Jul 15 '14 at 21:42
The two processes want to access the resources locked by the other process and block each other.
The following blog is on deadlock prevention:
- 496
- 4
- 18