|
Home > Archive > MS SQL Server > April 2006 > Update Lock (Preventing deadlock)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Update Lock (Preventing deadlock)
|
|
|
| Hi,
How does the update lock (before obtaining exclusive lock) can prevent
deadlocks? In what situations?
This is what BOL says about this type of lock:
------------------------
If two transactions acquire shared-mode locks on a resource and then attempt
to update data concurrently, one transaction attempts the lock conversion to
an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must
wait because the exclusive lock for one transaction is not compatible with
the shared-mode lock of the other transaction; a lock wait occurs. The
second transaction attempts to acquire an exclusive (X) lock for its update.
Because both transactions are converting to exclusive (X) locks, and they
are each waiting for the other transaction to release its shared-mode lock,
a deadlock occurs.
To avoid this potential deadlock problem, update (U) locks are used. Only
one transaction can obtain an update (U) lock to a resource at a time.
------------------------
The situation that BOL describes is exactly what I encountered with
deadlock:
1) two users use REPEATABLE READ isolation level that needs shared lock for
read operations.
2) Both of them begin a transaction and select entire table1.
3) First user issues an UPDATE command on the table1. This is blocked
(update lock is acquired.)
4) Second user issues an UPDATE command too. Deadlock occurs!
I need some clarification that why the deadlock happened even by having
update lock. When update lock prevents a deadlock?
Thanks in advance,
Leila
| |
| Kalen Delaney 2006-04-04, 8:23 pm |
| Hi Leila
Shared locks can be held by multiple processes at the same time.
Exclusive locks will be blocked and will block any process's shared or
exclusive lock.
Update locks are compatible with shared locks, but not with other update
locks or exclusive locks.
Update locks are acquired automatically while you are searching for data
with the intension of updating it. As soon as you find the data you want to
modify, your update lock is converted to an exclusive lock. So if two
processes are both searching for the same data , the first one to get to it
will get the update lock, then the second one will not be able to get an
update lock, and will have to just wait.
It sounds like both your processes already have gotten Shared locks on the
same data. Then one of them gets an update lock, and tries to convert to
exclusive, and is blocked. Then the second one tries to get an update lock,
and a deadlock occurs.
Your problem is that both processes ALREADY have shared locks on the same
data before trying to update it. SQL Server had no way of knowing that the
processes were eventually going to try to modify the data.
The solution here is to force an update lock when you select from the table.
You will not need REPEATABLE READ isolation as long as you are in a
transaction.
The first process will get the update lock, and the second one will wait. No
deadlock will occur.
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"Leila" <Leilas@hotpop.com> wrote in message
news:%23CeOyRDWGHA.4924@TK2MSFTNGP05.phx.gbl...
> Hi,
> How does the update lock (before obtaining exclusive lock) can prevent
> deadlocks? In what situations?
> This is what BOL says about this type of lock:
> ------------------------
> If two transactions acquire shared-mode locks on a resource and then
> attempt to update data concurrently, one transaction attempts the lock
> conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock
> conversion must wait because the exclusive lock for one transaction is not
> compatible with the shared-mode lock of the other transaction; a lock wait
> occurs. The second transaction attempts to acquire an exclusive (X) lock
> for its update. Because both transactions are converting to exclusive (X)
> locks, and they are each waiting for the other transaction to release its
> shared-mode lock, a deadlock occurs.
> To avoid this potential deadlock problem, update (U) locks are used. Only
> one transaction can obtain an update (U) lock to a resource at a time.
> ------------------------
> The situation that BOL describes is exactly what I encountered with
> deadlock:
> 1) two users use REPEATABLE READ isolation level that needs shared lock
> for read operations.
> 2) Both of them begin a transaction and select entire table1.
> 3) First user issues an UPDATE command on the table1. This is blocked
> (update lock is acquired.)
> 4) Second user issues an UPDATE command too. Deadlock occurs!
>
> I need some clarification that why the deadlock happened even by having
> update lock. When update lock prevents a deadlock?
> Thanks in advance,
> Leila
>
>
>
>
>
| |
| Brian Selzer 2006-04-05, 3:23 am |
| An UPDATE always obtains and holds an exclusive lock for the remaining
duration of the transaction.
Unless the transaction isolation level is READ UNCOMMITTED, an unadorned
SELECT will obtain and may possibly hold a shared lock
Update locks must be explicitly specified.
SELECT...WITH(UPDLOCK) obtains a single update lock (unless the transaction
isolation level is serializable, in which case it obtains an update
range-lock.
SELECT...WITH(UPDLOCK, HOLDLOCK) obtains an update range-lock.
Here's an example of how the update lock prevents deadlocks:
Assume that you have the following code in a stored procedure:
DECLARE @LastID INT
BEGIN TRAN
SELECT @LastID = MAX(ID)
FROM tableName WITH(UPDLOCK, HOLDLOCK)
IF @LastID IS NULL SET @LastID = 0
SET @LastID = @LastID + 1
INSERT tableName (ID, Value1)
VALUES(@LastID, @Value1)
COMMIT
Without the locking hint, WITH(UPDLOCK, HOLDLOCK), it's possible for the
same code to be executing at the same time. This means that it's possible
for the SELECT statement to be executing on two connections at the same
time, resulting in shared locks being held for the same resource on more
than one connection, which prevents either transaction from obtaining an
exclusive lock so that an INSERT can take place.
"Leila" <Leilas@hotpop.com> wrote in message
news:%23CeOyRDWGHA.4924@TK2MSFTNGP05.phx.gbl...
> Hi,
> How does the update lock (before obtaining exclusive lock) can prevent
> deadlocks? In what situations?
> This is what BOL says about this type of lock:
> ------------------------
> If two transactions acquire shared-mode locks on a resource and then
> attempt to update data concurrently, one transaction attempts the lock
> conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock
> conversion must wait because the exclusive lock for one transaction is not
> compatible with the shared-mode lock of the other transaction; a lock wait
> occurs. The second transaction attempts to acquire an exclusive (X) lock
> for its update. Because both transactions are converting to exclusive (X)
> locks, and they are each waiting for the other transaction to release its
> shared-mode lock, a deadlock occurs.
> To avoid this potential deadlock problem, update (U) locks are used. Only
> one transaction can obtain an update (U) lock to a resource at a time.
> ------------------------
> The situation that BOL describes is exactly what I encountered with
> deadlock:
> 1) two users use REPEATABLE READ isolation level that needs shared lock
> for read operations.
> 2) Both of them begin a transaction and select entire table1.
> 3) First user issues an UPDATE command on the table1. This is blocked
> (update lock is acquired.)
> 4) Second user issues an UPDATE command too. Deadlock occurs!
>
> I need some clarification that why the deadlock happened even by having
> update lock. When update lock prevents a deadlock?
> Thanks in advance,
> Leila
>
>
>
>
>
| |
|
| Thanks Kalen!
Can I conclude that if I'm going to select and update a table within my SP,
UPDLOCK hint must be specified for select command? Or it depends on whether
my select holds a shared lock (i.e. because of REPEATABLE READ isolation
level) or not?
I think without a shared lock, my select doesn't need UPDLOCK because
there's not chance for deadlock. Is it true?
"Kalen Delaney" < replies@public_newsg
roups.com> wrote in message
news:Ouk7MTEWGHA.4972@TK2MSFTNGP02.phx.gbl...
> Hi Leila
>
> Shared locks can be held by multiple processes at the same time.
> Exclusive locks will be blocked and will block any process's shared or
> exclusive lock.
>
> Update locks are compatible with shared locks, but not with other update
> locks or exclusive locks.
> Update locks are acquired automatically while you are searching for data
> with the intension of updating it. As soon as you find the data you want
> to modify, your update lock is converted to an exclusive lock. So if two
> processes are both searching for the same data , the first one to get to
> it will get the update lock, then the second one will not be able to get
> an update lock, and will have to just wait.
>
> It sounds like both your processes already have gotten Shared locks on the
> same data. Then one of them gets an update lock, and tries to convert to
> exclusive, and is blocked. Then the second one tries to get an update
> lock, and a deadlock occurs.
>
> Your problem is that both processes ALREADY have shared locks on the same
> data before trying to update it. SQL Server had no way of knowing that the
> processes were eventually going to try to modify the data.
>
> The solution here is to force an update lock when you select from the
> table. You will not need REPEATABLE READ isolation as long as you are in a
> transaction.
> The first process will get the update lock, and the second one will wait.
> No deadlock will occur.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www. solidqualitylearning
.com
>
>
> "Leila" <Leilas@hotpop.com> wrote in message
> news:%23CeOyRDWGHA.4924@TK2MSFTNGP05.phx.gbl...
>
>
| |
|
| Thanks Brian!
Nice example!
"Brian Selzer" <brian@selzer-software.com> wrote in message
news:ObLCW0GWGHA.1204@TK2MSFTNGP04.phx.gbl...
> An UPDATE always obtains and holds an exclusive lock for the remaining
> duration of the transaction.
>
> Unless the transaction isolation level is READ UNCOMMITTED, an unadorned
> SELECT will obtain and may possibly hold a shared lock
>
> Update locks must be explicitly specified.
>
> SELECT...WITH(UPDLOCK) obtains a single update lock (unless the
> transaction isolation level is serializable, in which case it obtains an
> update range-lock.
>
> SELECT...WITH(UPDLOCK, HOLDLOCK) obtains an update range-lock.
>
>
> Here's an example of how the update lock prevents deadlocks:
>
> Assume that you have the following code in a stored procedure:
>
> DECLARE @LastID INT
> BEGIN TRAN
> SELECT @LastID = MAX(ID)
> FROM tableName WITH(UPDLOCK, HOLDLOCK)
> IF @LastID IS NULL SET @LastID = 0
> SET @LastID = @LastID + 1
> INSERT tableName (ID, Value1)
> VALUES(@LastID, @Value1)
> COMMIT
>
> Without the locking hint, WITH(UPDLOCK, HOLDLOCK), it's possible for the
> same code to be executing at the same time. This means that it's possible
> for the SELECT statement to be executing on two connections at the same
> time, resulting in shared locks being held for the same resource on more
> than one connection, which prevents either transaction from obtaining an
> exclusive lock so that an INSERT can take place.
>
>
>
>
>
> "Leila" <Leilas@hotpop.com> wrote in message
> news:%23CeOyRDWGHA.4924@TK2MSFTNGP05.phx.gbl...
>
>
| |
| Erland Sommarskog 2006-04-05, 8:23 pm |
| Brian Selzer (brian@selzer-software.com) writes:
> Assume that you have the following code in a stored procedure:
>
> DECLARE @LastID INT
> BEGIN TRAN
> SELECT @LastID = MAX(ID)
> FROM tableName WITH(UPDLOCK, HOLDLOCK)
> IF @LastID IS NULL SET @LastID = 0
> SET @LastID = @LastID + 1
> INSERT tableName (ID, Value1)
> VALUES(@LastID, @Value1)
> COMMIT
>
> Without the locking hint, WITH(UPDLOCK, HOLDLOCK), it's possible for the
> same code to be executing at the same time. This means that it's possible
> for the SELECT statement to be executing on two connections at the same
> time, resulting in shared locks being held for the same resource on more
> than one connection, which prevents either transaction from obtaining an
> exclusive lock so that an INSERT can take place.
Actually, without any locking hint at all, and with the default
isolation level, you will not get a deadlock here if two processes
run this procedure in parallel. Instead of the processes will get a
PK violation error. (Which I know that you know well, but I think we
need to explain it for Leila.)
I guess that if you set the isolation level to REPEATABLE READ, the
same happens, but I haven't tested.
If you have only HOLDLOCK, there will be a deadlock. The same goes if
you just set the transaction isolation level to SERIALIZABLE and don't
use any hint.
If there is only UPDLOCK, it may work, but I recall you pointed out
that there could be a PK violation under some circumstances.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| Brian Selzer 2006-04-07, 3:24 am |
|
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns979D9EC16039
Yazorman@127.0.0.1...
> Brian Selzer (brian@selzer-software.com) writes:
>
> Actually, without any locking hint at all, and with the default
> isolation level, you will not get a deadlock here if two processes
> run this procedure in parallel. Instead of the processes will get a
> PK violation error. (Which I know that you know well, but I think we
> need to explain it for Leila.)
>
Thank you for clarifying this.
> I guess that if you set the isolation level to REPEATABLE READ, the
> same happens, but I haven't tested.
>
REPEATABLE READ doesn't apply range-locks, so yes, the same happens.
> If you have only HOLDLOCK, there will be a deadlock. The same goes if
> you just set the transaction isolation level to SERIALIZABLE and don't
> use any hint.
>
> If there is only UPDLOCK, it may work, but I recall you pointed out
> that there could be a PK violation under some circumstances.
>
Yes, individual update locks won't prevent inserts like a range-lock, so you
can get a PK violation, but not from running the same procedure concurrently
because the update lock on the row with the currently committed maximum
value (the max row) can only be applied by one instance of the procedure at
a time. If the procedure can be bypassed, or if there is more then one
procedure that inserts, then unless all instances apply an update lock on
the max row, it is possible for a PK violation to occur.
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
|
|
|
|
|