Update Lock

microsoft.public.sqlserver.programming

16 Sep 2005 9:21 PM

Leila

Hi,
In the description of Update Lock in BOL, it is mentioned that Update Lock
can eliminate deadlocks because only one transaction at a time can obtain
it.
Personally I'm not convinced by this logic. Can an Exclusive Lock be
obtained by two transactions at a time? What if there was such a rule for
Exclusive Locks that it could not be obtained by two transactions at the
same time, like Update Locks? Then the second transaction could not try to
deadlock with the first one...
I'm sure there is something misunderstood by me!
Thanks in advance,
Leila

16 Sep 2005 9:46 PM

Brian Selzer

In Books Online, it says that update locks can prevent a common form of
deadlock--not all deadlocks. Here's the relevant two paragraphs:

Update (U) locks prevent a common form of deadlock. A typical update pattern
consists of a transaction reading a record, acquiring a shared (S) lock on
the resource (page or row), and then modifying the row, which requires lock
conversion to an exclusive (X) 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. If a
transaction modifies a resource, the update (U) lock is converted to an
exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.

<<
An update lock does not prevent reads like an exclusive lock. You can
obtain a shared lock if an update lock is being held by another transaction.
You cannot obtain an exclusive lock or an update lock, however. In
addition, you can obtain an update lock if a shared lock is held by another
transaction. Update locks scale better than exclusive locks, because they
aren't blocked by and don't block other transactions that are only reading
the information.

Show quote

"Leila" <Lei***@hotpop.com> wrote in message
news:eEhv3SwuFHA.2792@tk2msftngp13.phx.gbl...
> Hi,
> In the description of Update Lock in BOL, it is mentioned that Update Lock
> can eliminate deadlocks because only one transaction at a time can obtain
> it.
> Personally I'm not convinced by this logic. Can an Exclusive Lock be
> obtained by two transactions at a time? What if there was such a rule for
> Exclusive Locks that it could not be obtained by two transactions at the
> same time, like Update Locks? Then the second transaction could not try to
> deadlock with the first one...
> I'm sure there is something misunderstood by me!
> Thanks in advance,
> Leila
>
>

16 Sep 2005 10:02 PM

Hugo Kornelis

On Sat, 17 Sep 2005 01:51:33 +0430, Leila wrote:

>Hi,
>In the description of Update Lock in BOL, it is mentioned that Update Lock
>can eliminate deadlocks because only one transaction at a time can obtain
>it.
>Personally I'm not convinced by this logic. Can an Exclusive Lock be
>obtained by two transactions at a time? What if there was such a rule for
>Exclusive Locks that it could not be obtained by two transactions at the
>same time, like Update Locks? Then the second transaction could not try to
>deadlock with the first one...
>I'm sure there is something misunderstood by me!
>Thanks in advance,
>Leila
>

Hi Leila,

Both update lock and exclusive lock can be obtained by only one
transaction at a time. Both can be used to eliminate deadlocks. But
exclusive locks can eliminate concurrency as well.

Here's how update locks work:
- If a transaction needs to read data that it definitely won't change,
use a regular shared lock. The lock will be granted, unless there is an
exclusive lock right now.
- If a transaction needs to read data that it might want to change at a
later time (or that it definitely will change at a later time), use an
update lock. The lock will be granted even if there are shared locks
active, but not if there is another update lock or an exclusive lock.
- If a transaction needs to change data, it takes an exclusive lock (or
upgrades an existing update lock to an exclusive lock). If there are
currently shared locks taken on the row, you'll have to wait until they
are released.

And here is how this eliminates deadlocks. Consider two transactions
that read a row first, and update it later, and that are started almost
simultaneously. Without update locks:
- Tran#1 requests a shared lock on the row, and gets it.
- Tran#2 requests a shared lock on the row, and gets it (since shared
locks can co-exist)
- Tran#1 requests to upgrade the shared lock to an exclusive lock, and
is blocked until the concurrent shared lock from Tran#2 is released.
- Tran#2 requests to upgrade the shared lock to an exclusive lock, and
is blocked until the concurrent shared lock from Tran#1 is released.
And now, we have Tran#1 and Tran#2, mutually blocking each other.
Deadlock.

With update locks, things look different:
- Tran#1 requests an update lock on the row, and gets it.
- Tran#2 requests an update lock on the row, and is blocked until the
concurrent update lock from Tran#1 is released.
- Tran#1 requests to upgrade the update lock to an exclusive lock, and
gets it.
- Tran#1 finishes and releases it's locks.
- Tran#2 now gets the update lock it requested.
- Tran#2 requests to upgrade the shared lock to an exclusive lock, and
gets it.
- Tran#2 finishes and releases it's locks.
No deadlocks. And a third transaction that only wants to read the row
won't be blocked while Tran#1 or Tran#2 is holding the update lock
(though it will be blocked while the other transactions have the lock
upgraded to exclusive - but that should be a matter of milliseconds).

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

17 Sep 2005 5:49 AM

Leila

Thanks for great explanation Hugo!
What would happen if S locks could convert to X locks? Using your example:
- Tran 1 gets a S lock on the resource by reading that
- Tran 2 gets a S lock on the resource by reading that
- Tran 1 tries to get X lock, and because there's no other X lock, it can
obtain it
- Tran 2 tries to get X lock, and because X locks cannot co-exist, it is
blocked

This is exactly identical to your second example, with no update lock.

Thanks,
Leila

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:6jfmi15efb9budd85fn98r9smtt2k3qssp@4ax.com...
> On Sat, 17 Sep 2005 01:51:33 +0430, Leila wrote:
>
> >Hi,
> >In the description of Update Lock in BOL, it is mentioned that Update
Lock
> >can eliminate deadlocks because only one transaction at a time can obtain
> >it.
> >Personally I'm not convinced by this logic. Can an Exclusive Lock be
> >obtained by two transactions at a time? What if there was such a rule for
> >Exclusive Locks that it could not be obtained by two transactions at the
> >same time, like Update Locks? Then the second transaction could not try
to
> >deadlock with the first one...
> >I'm sure there is something misunderstood by me!
> >Thanks in advance,
> >Leila
> >
>
> Hi Leila,
>
> Both update lock and exclusive lock can be obtained by only one
> transaction at a time. Both can be used to eliminate deadlocks. But
> exclusive locks can eliminate concurrency as well.
>
> Here's how update locks work:
> - If a transaction needs to read data that it definitely won't change,
> use a regular shared lock. The lock will be granted, unless there is an
> exclusive lock right now.
> - If a transaction needs to read data that it might want to change at a
> later time (or that it definitely will change at a later time), use an
> update lock. The lock will be granted even if there are shared locks
> active, but not if there is another update lock or an exclusive lock.
> - If a transaction needs to change data, it takes an exclusive lock (or
> upgrades an existing update lock to an exclusive lock). If there are
> currently shared locks taken on the row, you'll have to wait until they
> are released.
>
> And here is how this eliminates deadlocks. Consider two transactions
> that read a row first, and update it later, and that are started almost
> simultaneously. Without update locks:
> - Tran#1 requests a shared lock on the row, and gets it.
> - Tran#2 requests a shared lock on the row, and gets it (since shared
> locks can co-exist)
> - Tran#1 requests to upgrade the shared lock to an exclusive lock, and
> is blocked until the concurrent shared lock from Tran#2 is released.
> - Tran#2 requests to upgrade the shared lock to an exclusive lock, and
> is blocked until the concurrent shared lock from Tran#1 is released.
> And now, we have Tran#1 and Tran#2, mutually blocking each other.
> Deadlock.
>
> With update locks, things look different:
> - Tran#1 requests an update lock on the row, and gets it.
> - Tran#2 requests an update lock on the row, and is blocked until the
> concurrent update lock from Tran#1 is released.
> - Tran#1 requests to upgrade the update lock to an exclusive lock, and
> gets it.
> - Tran#1 finishes and releases it's locks.
> - Tran#2 now gets the update lock it requested.
> - Tran#2 requests to upgrade the shared lock to an exclusive lock, and
> gets it.
> - Tran#2 finishes and releases it's locks.
> No deadlocks. And a third transaction that only wants to read the row
> won't be blocked while Tran#1 or Tran#2 is holding the update lock
> (though it will be blocked while the other transactions have the lock
> upgraded to exclusive - but that should be a matter of milliseconds).
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

17 Sep 2005 6:19 AM

Brian Selzer

The problem with your logic is that Tran 1 will never obtain an X lock
because Tran 2 is holding an S lock This causes a deadlock, because Tran 1
cannot obtain an X lock as long as Tran 2 has an S lock and Tran 2 cannot
obtain an X lock while Tran 1 has an S lock.

Look up lock compatibility in BOL. There's a table describing which locks
can be obtained if another transaction already has a lock.

Show quote

"Leila" <Lei***@hotpop.com> wrote in message
news:OCnkzu0uFHA.1256@TK2MSFTNGP09.phx.gbl...
> Thanks for great explanation Hugo!
> What would happen if S locks could convert to X locks? Using your example:
> - Tran 1 gets a S lock on the resource by reading that
> - Tran 2 gets a S lock on the resource by reading that
> - Tran 1 tries to get X lock, and because there's no other X lock, it can
> obtain it
> - Tran 2 tries to get X lock, and because X locks cannot co-exist, it is
> blocked
>
> This is exactly identical to your second example, with no update lock.
>
> Thanks,
> Leila
>
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> news:6jfmi15efb9budd85fn98r9smtt2k3qssp@4ax.com...
> > On Sat, 17 Sep 2005 01:51:33 +0430, Leila wrote:
> >
> > >Hi,
> > >In the description of Update Lock in BOL, it is mentioned that Update
> Lock
> > >can eliminate deadlocks because only one transaction at a time can
obtain
> > >it.
> > >Personally I'm not convinced by this logic. Can an Exclusive Lock be
> > >obtained by two transactions at a time? What if there was such a rule
for
> > >Exclusive Locks that it could not be obtained by two transactions at
the
> > >same time, like Update Locks? Then the second transaction could not try
> to
> > >deadlock with the first one...
> > >I'm sure there is something misunderstood by me!
> > >Thanks in advance,
> > >Leila
> > >
> >
> > Hi Leila,
> >
> > Both update lock and exclusive lock can be obtained by only one
> > transaction at a time. Both can be used to eliminate deadlocks. But
> > exclusive locks can eliminate concurrency as well.
> >
> > Here's how update locks work:
> > - If a transaction needs to read data that it definitely won't change,
> > use a regular shared lock. The lock will be granted, unless there is an
> > exclusive lock right now.
> > - If a transaction needs to read data that it might want to change at a
> > later time (or that it definitely will change at a later time), use an
> > update lock. The lock will be granted even if there are shared locks
> > active, but not if there is another update lock or an exclusive lock.
> > - If a transaction needs to change data, it takes an exclusive lock (or
> > upgrades an existing update lock to an exclusive lock). If there are
> > currently shared locks taken on the row, you'll have to wait until they
> > are released.
> >
> > And here is how this eliminates deadlocks. Consider two transactions
> > that read a row first, and update it later, and that are started almost
> > simultaneously. Without update locks:
> > - Tran#1 requests a shared lock on the row, and gets it.
> > - Tran#2 requests a shared lock on the row, and gets it (since shared
> > locks can co-exist)
> > - Tran#1 requests to upgrade the shared lock to an exclusive lock, and
> > is blocked until the concurrent shared lock from Tran#2 is released.
> > - Tran#2 requests to upgrade the shared lock to an exclusive lock, and
> > is blocked until the concurrent shared lock from Tran#1 is released.
> > And now, we have Tran#1 and Tran#2, mutually blocking each other.
> > Deadlock.
> >
> > With update locks, things look different:
> > - Tran#1 requests an update lock on the row, and gets it.
> > - Tran#2 requests an update lock on the row, and is blocked until the
> > concurrent update lock from Tran#1 is released.
> > - Tran#1 requests to upgrade the update lock to an exclusive lock, and
> > gets it.
> > - Tran#1 finishes and releases it's locks.
> > - Tran#2 now gets the update lock it requested.
> > - Tran#2 requests to upgrade the shared lock to an exclusive lock, and
> > gets it.
> > - Tran#2 finishes and releases it's locks.
> > No deadlocks. And a third transaction that only wants to read the row
> > won't be blocked while Tran#1 or Tran#2 is holding the update lock
> > (though it will be blocked while the other transactions have the lock
> > upgraded to exclusive - but that should be a matter of milliseconds).
> >
> > Best, Hugo
> > --
> >
> > (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>

17 Sep 2005 11:58 AM

Leila

Thanks Brian,
I agree! My logic cannot work because of the lock compatibility issue. But I
mean what if my logic was used in SQL Server for lock compatibility? Why U
lock exists?
Certainly there's a reason that Microsoft has introduced U locks. Unless my
example in previous post can be logical.

Show quote

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:OGjn6#0uFHA.2504@tk2msftngp13.phx.gbl...
> The problem with your logic is that Tran 1 will never obtain an X lock
> because Tran 2 is holding an S lock This causes a deadlock, because Tran
1
> cannot obtain an X lock as long as Tran 2 has an S lock and Tran 2 cannot
> obtain an X lock while Tran 1 has an S lock.
>
> Look up lock compatibility in BOL. There's a table describing which locks
> can be obtained if another transaction already has a lock.
>
>
> "Leila" <Lei***@hotpop.com> wrote in message
> news:OCnkzu0uFHA.1256@TK2MSFTNGP09.phx.gbl...
> > Thanks for great explanation Hugo!
> > What would happen if S locks could convert to X locks? Using your
example:
> > - Tran 1 gets a S lock on the resource by reading that
> > - Tran 2 gets a S lock on the resource by reading that
> > - Tran 1 tries to get X lock, and because there's no other X lock, it
can
> > obtain it
> > - Tran 2 tries to get X lock, and because X locks cannot co-exist, it is
> > blocked
> >
> > This is exactly identical to your second example, with no update lock.
> >
> > Thanks,
> > Leila
> >
> >
> > "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> > news:6jfmi15efb9budd85fn98r9smtt2k3qssp@4ax.com...
> > > On Sat, 17 Sep 2005 01:51:33 +0430, Leila wrote:
> > >
> > > >Hi,
> > > >In the description of Update Lock in BOL, it is mentioned that Update
> > Lock
> > > >can eliminate deadlocks because only one transaction at a time can
> obtain
> > > >it.
> > > >Personally I'm not convinced by this logic. Can an Exclusive Lock be
> > > >obtained by two transactions at a time? What if there was such a rule
> for
> > > >Exclusive Locks that it could not be obtained by two transactions at
> the
> > > >same time, like Update Locks? Then the second transaction could not
try
> > to
> > > >deadlock with the first one...
> > > >I'm sure there is something misunderstood by me!
> > > >Thanks in advance,
> > > >Leila
> > > >
> > >
> > > Hi Leila,
> > >
> > > Both update lock and exclusive lock can be obtained by only one
> > > transaction at a time. Both can be used to eliminate deadlocks. But
> > > exclusive locks can eliminate concurrency as well.
> > >
> > > Here's how update locks work:
> > > - If a transaction needs to read data that it definitely won't change,
> > > use a regular shared lock. The lock will be granted, unless there is
an
> > > exclusive lock right now.
> > > - If a transaction needs to read data that it might want to change at
a
> > > later time (or that it definitely will change at a later time), use an
> > > update lock. The lock will be granted even if there are shared locks
> > > active, but not if there is another update lock or an exclusive lock.
> > > - If a transaction needs to change data, it takes an exclusive lock
(or
> > > upgrades an existing update lock to an exclusive lock). If there are
> > > currently shared locks taken on the row, you'll have to wait until
they
> > > are released.
> > >
> > > And here is how this eliminates deadlocks. Consider two transactions
> > > that read a row first, and update it later, and that are started
almost
> > > simultaneously. Without update locks:
> > > - Tran#1 requests a shared lock on the row, and gets it.
> > > - Tran#2 requests a shared lock on the row, and gets it (since shared
> > > locks can co-exist)
> > > - Tran#1 requests to upgrade the shared lock to an exclusive lock, and
> > > is blocked until the concurrent shared lock from Tran#2 is released.
> > > - Tran#2 requests to upgrade the shared lock to an exclusive lock, and
> > > is blocked until the concurrent shared lock from Tran#1 is released.
> > > And now, we have Tran#1 and Tran#2, mutually blocking each other.
> > > Deadlock.
> > >
> > > With update locks, things look different:
> > > - Tran#1 requests an update lock on the row, and gets it.
> > > - Tran#2 requests an update lock on the row, and is blocked until the
> > > concurrent update lock from Tran#1 is released.
> > > - Tran#1 requests to upgrade the update lock to an exclusive lock, and
> > > gets it.
> > > - Tran#1 finishes and releases it's locks.
> > > - Tran#2 now gets the update lock it requested.
> > > - Tran#2 requests to upgrade the shared lock to an exclusive lock, and
> > > gets it.
> > > - Tran#2 finishes and releases it's locks.
> > > No deadlocks. And a third transaction that only wants to read the row
> > > won't be blocked while Tran#1 or Tran#2 is holding the update lock
> > > (though it will be blocked while the other transactions have the lock
> > > upgraded to exclusive - but that should be a matter of milliseconds).
> > >
> > > Best, Hugo
> > > --
> > >
> > > (Remove _NO_ and _SPAM_ to get my e-mail address)
> >
> >
>
>

17 Sep 2005 8:36 PM

Hugo Kornelis

On Sat, 17 Sep 2005 16:28:44 +0430, Leila wrote:

>Thanks Brian,
>I agree! My logic cannot work because of the lock compatibility issue. But I
>mean what if my logic was used in SQL Server for lock compatibility?

Hi Leila,

Well, for starters, any illusion of getting coherent results from the
server would be down the drain.

An X lock is used when rows are changed, but the change is not yet
committed. There is a chance that the chance will be rolled back; if
that happens, the changed data has (logically) never been in the
database.

If an X lock and an S lock are permitted to co-exist, things like the
following could happen:

Scenario #1:
- You perform an UPDATE. An X lock is taken, the data is changed and SQL
Server start checking constraints and executing triggers.
- In the few milliseconds that takes, I read data from the table. The S
lock is granted because of your logic, so I get to see your data.
- Your transaction is rolled back, because the trigger detected a
violation of a business rule.
- But in the meantime, I am looking at your data. I see that the
business rule is violated, so I call the DBA, telling him that the
database is corrupt. He immediately brings down the server and starts
investigating. Of course, he can't find any corrupt data. In the end,
you, me and 50 colleagues have been shut out from the system for over an
hour, the DBA is pissed and I am fired for bothering the DBA with
poblems that don't really exist.

Scenario #2:
- You start a long transaction, that first reads some data. You'll need
to read the same data again later in the transaction, and it's critical
that the data is unchanged, so you set the transacion isolation level to
repeatable read. The S lock is granted, and is not released after the
SELECT statement finishes.
- In the mean time, I do an UPDATE. The X lock I request is granted
because of your logic; the data is changed and my transaction is
successfully committed.
- After a few minutes, your long-running transaction re-reads the data.
It has now changed. Read repeateable apparently isn't.

Scenario #3:
- I do a SELECT that is executed using an index seek followed by a
bookmark lookup.
- At the same time, yoou start a DELETE that will delete some of the
rows in the table. Because your X lock and my S lock are allowed to
co-exist, the queries are running concurrently.
- If the timing is exact right, SQL Server might find an index entry
that matches the search arguments in my query, do a bookmark lookup,
then find nothing because the row has just been deleted. BLAM. Internal
error, and we end up calling Microsoft for support.

These and other doom scenario's are the reason that your logic can't be
used - an exclusive lock really needs to exclude all other locks (as the
name already suggests).

> Why U lock exists?

Because S and X locks can't co-exist. And with just S and X lock,
there's a good chance to get deadlocks (as I explained in my previous
message), that can be prevented by the U lock.

In plain English:
S means: I need it, but others are free to use it as well.
X means: I need it, and nobody else can have it.
U means: I need it, others are free to use it as well, but I do reserve
the right to get an X lock later.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

18 Sep 2005 6:54 PM

Leila

Great scenarios!

Scenario 1:
When I'm updating and holding X lock, you cannot obtain S lock. Your
transaction will wait until i commit or rollback (My suggested logic was not
being able to get S lock over X lock!).

Scenario 2:
This is exactly the situation that my logic will not work!

I think I need a scenario that shows how U lock eliminates deadlock. It will
help me a lot!

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:fauoi111gh1pnl10i2a7v3822lmrndamr8@4ax.com...
> On Sat, 17 Sep 2005 16:28:44 +0430, Leila wrote:
>
> >Thanks Brian,
> >I agree! My logic cannot work because of the lock compatibility issue.
But I
> >mean what if my logic was used in SQL Server for lock compatibility?
>
> Hi Leila,
>
> Well, for starters, any illusion of getting coherent results from the
> server would be down the drain.
>
> An X lock is used when rows are changed, but the change is not yet
> committed. There is a chance that the chance will be rolled back; if
> that happens, the changed data has (logically) never been in the
> database.
>
> If an X lock and an S lock are permitted to co-exist, things like the
> following could happen:
>
> Scenario #1:
> - You perform an UPDATE. An X lock is taken, the data is changed and SQL
> Server start checking constraints and executing triggers.
> - In the few milliseconds that takes, I read data from the table. The S
> lock is granted because of your logic, so I get to see your data.
> - Your transaction is rolled back, because the trigger detected a
> violation of a business rule.
> - But in the meantime, I am looking at your data. I see that the
> business rule is violated, so I call the DBA, telling him that the
> database is corrupt. He immediately brings down the server and starts
> investigating. Of course, he can't find any corrupt data. In the end,
> you, me and 50 colleagues have been shut out from the system for over an
> hour, the DBA is pissed and I am fired for bothering the DBA with
> poblems that don't really exist.
>
> Scenario #2:
> - You start a long transaction, that first reads some data. You'll need
> to read the same data again later in the transaction, and it's critical
> that the data is unchanged, so you set the transacion isolation level to
> repeatable read. The S lock is granted, and is not released after the
> SELECT statement finishes.
> - In the mean time, I do an UPDATE. The X lock I request is granted
> because of your logic; the data is changed and my transaction is
> successfully committed.
> - After a few minutes, your long-running transaction re-reads the data.
> It has now changed. Read repeateable apparently isn't.
>
> Scenario #3:
> - I do a SELECT that is executed using an index seek followed by a
> bookmark lookup.
> - At the same time, yoou start a DELETE that will delete some of the
> rows in the table. Because your X lock and my S lock are allowed to
> co-exist, the queries are running concurrently.
> - If the timing is exact right, SQL Server might find an index entry
> that matches the search arguments in my query, do a bookmark lookup,
> then find nothing because the row has just been deleted. BLAM. Internal
> error, and we end up calling Microsoft for support.
>
> These and other doom scenario's are the reason that your logic can't be
> used - an exclusive lock really needs to exclude all other locks (as the
> name already suggests).
>
> > Why U lock exists?
>
> Because S and X locks can't co-exist. And with just S and X lock,
> there's a good chance to get deadlocks (as I explained in my previous
> message), that can be prevented by the U lock.
>
> In plain English:
> S means: I need it, but others are free to use it as well.
> X means: I need it, and nobody else can have it.
> U means: I need it, others are free to use it as well, but I do reserve
> the right to get an X lock later.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

18 Sep 2005 8:58 PM

Hugo Kornelis

On Sun, 18 Sep 2005 23:24:06 +0430, Leila wrote:

>Great scenarios!
>
>Scenario 1:
>When I'm updating and holding X lock, you cannot obtain S lock. Your
>transaction will wait until i commit or rollback (My suggested logic was not
>being able to get S lock over X lock!).
>
>Scenario 2:
>This is exactly the situation that my logic will not work!
>
>I think I need a scenario that shows how U lock eliminates deadlock. It will
>help me a lot!

Hi Leila,

Sorry for misinterpreting your suggested logic. I'm glad one of my other
scenario's did address the problem!

I already gave a scenario for U lock in my first message in this
discussion. I'll repeat it, and at the same time extend it a bit to show
how U lock manages to prevent deadlocks while at the same time keeping
concurrency as high as possible.

I'll first present the how the scenario would go with only S and X
locks; afdter that, I'll repeat it with S, X, and U locks.

Version 1: S and X locks only.
1. You need to read a row. S lock is requested and granted; row is
returned.
2. I need to read the same row. S lock is requested and granted (since
two S locks can co-exist); row is returned.
3. Manager starts monthly report that aggregates many rows. S locks are
requested and granted (three S-locks may co-exist on "our" row, and
other rows were not locked at all).
4. You want to update the row. Upgrade from S lock to X lock is
requested. Since X lock can't co-exist with S locks from me and manager,
your transaction is blocked until those are released.
5. Manager's report is finished; locks are released. Your transaction is
still waiting for my S lock to be lifted as well.
6. I want to update the same row. Upgrade from S lock to X lock is
requested. Since X lock can't co-exist with your S lock, my transaction
is blocked until that is released.
7. Soon, the deadlock manager will see that are mutually blocking each
other, and that neither of us is prepared to release the lock that
blocks the other. One of our processes is chosen as deadlock victim and
rolled back.
8. After that, the other process gets it's X lock, the update is carried
out and the X lock is released.

Version 2: S, U, and X locks.
1. You need to read a row. Since you might want to change it later, U
lock is requested and granted; row is returned.
2. I need to read the same row. I also might want to change it later, so
I request an U lock as well. Since no two U locks can co-exist, my
transaction is blocked and I'll have to wait until you release your
lock.
3. Manager starts monthly report that aggregates many rows. No changes
are done, so S lock are requested and granted (S-lock may co-exist with
U lock on "your" row, and other rows were not locked at all).
4. You want to update the row. Upgrade from U lock to X lock is
requested. Since X lock can't co-exist with S lock from manager, your
transaction is blocked until that one's released.
5. Manager's report is finished; locks are released. My transaction is
still blocked, but your transaction is no longer blocked and will
proceed.
6. Your U lock gets upgraded to X lock (this still won't unblock my
transaction); your update is carried out and when your transaction is
finished, your locks are released.
7. Now, I finally get my requested U lock, and the data from the row
(including your changes!)
8. Maybe seeing oyur changes means that I no longer need to change the
data; in that case I'll release the lock. Otherwise, I'll upgrade my U
lock to X lock, change the data, commit the transaction and release the
lock.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

20 Sep 2005 8:51 PM

Leila

Thanks Hugo,
Getting U lock is not a common scenario for developers. For example we
usually do a select after beginning the transaction and then perform an
update that causes a U lock to be requested, If other transaction is
currently holding S lock, I will be blocked. If he/she tries to get U lock
too, deadlock will happen.
If other transaction is holding U lock, then my S lock request will be
blocked which eliminates the deadlock. It seems the intention of updating
the row in future plays a key role and the programmer must see that. Can
getting U lock happen without using "lock hints" or necessarily I must use
them if I intend to perform modifications after doing the select?
Thanks again,
Leila

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:l9kri191cl8k45v0bi5eqgl623hrdf8vdt@4ax.com...
> On Sun, 18 Sep 2005 23:24:06 +0430, Leila wrote:
>
> >Great scenarios!
> >
> >Scenario 1:
> >When I'm updating and holding X lock, you cannot obtain S lock. Your
> >transaction will wait until i commit or rollback (My suggested logic was
not
> >being able to get S lock over X lock!).
> >
> >Scenario 2:
> >This is exactly the situation that my logic will not work!
> >
> >I think I need a scenario that shows how U lock eliminates deadlock. It
will
> >help me a lot!
>
> Hi Leila,
>
> Sorry for misinterpreting your suggested logic. I'm glad one of my other
> scenario's did address the problem!
>
> I already gave a scenario for U lock in my first message in this
> discussion. I'll repeat it, and at the same time extend it a bit to show
> how U lock manages to prevent deadlocks while at the same time keeping
> concurrency as high as possible.
>
> I'll first present the how the scenario would go with only S and X
> locks; afdter that, I'll repeat it with S, X, and U locks.
>
> Version 1: S and X locks only.
> 1. You need to read a row. S lock is requested and granted; row is
> returned.
> 2. I need to read the same row. S lock is requested and granted (since
> two S locks can co-exist); row is returned.
> 3. Manager starts monthly report that aggregates many rows. S locks are
> requested and granted (three S-locks may co-exist on "our" row, and
> other rows were not locked at all).
> 4. You want to update the row. Upgrade from S lock to X lock is
> requested. Since X lock can't co-exist with S locks from me and manager,
> your transaction is blocked until those are released.
> 5. Manager's report is finished; locks are released. Your transaction is
> still waiting for my S lock to be lifted as well.
> 6. I want to update the same row. Upgrade from S lock to X lock is
> requested. Since X lock can't co-exist with your S lock, my transaction
> is blocked until that is released.
> 7. Soon, the deadlock manager will see that are mutually blocking each
> other, and that neither of us is prepared to release the lock that
> blocks the other. One of our processes is chosen as deadlock victim and
> rolled back.
> 8. After that, the other process gets it's X lock, the update is carried
> out and the X lock is released.
>
> Version 2: S, U, and X locks.
> 1. You need to read a row. Since you might want to change it later, U
> lock is requested and granted; row is returned.
> 2. I need to read the same row. I also might want to change it later, so
> I request an U lock as well. Since no two U locks can co-exist, my
> transaction is blocked and I'll have to wait until you release your
> lock.
> 3. Manager starts monthly report that aggregates many rows. No changes
> are done, so S lock are requested and granted (S-lock may co-exist with
> U lock on "your" row, and other rows were not locked at all).
> 4. You want to update the row. Upgrade from U lock to X lock is
> requested. Since X lock can't co-exist with S lock from manager, your
> transaction is blocked until that one's released.
> 5. Manager's report is finished; locks are released. My transaction is
> still blocked, but your transaction is no longer blocked and will
> proceed.
> 6. Your U lock gets upgraded to X lock (this still won't unblock my
> transaction); your update is carried out and when your transaction is
> finished, your locks are released.
> 7. Now, I finally get my requested U lock, and the data from the row
> (including your changes!)
> 8. Maybe seeing oyur changes means that I no longer need to change the
> data; in that case I'll release the lock. Otherwise, I'll upgrade my U
> lock to X lock, change the data, commit the transaction and release the
> lock.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

22 Sep 2005 9:40 PM

Hugo Kornelis

On Wed, 21 Sep 2005 01:21:52 +0430, Leila wrote:

>Thanks Hugo,
>Getting U lock is not a common scenario for developers. For example we
>usually do a select after beginning the transaction and then perform an
>update that causes a U lock to be requested, If other transaction is
>currently holding S lock, I will be blocked. If he/she tries to get U lock
>too, deadlock will happen.

Hi Leila,

Sorry for the delayed reply.

The point of a U lock is that you get it INSTEAD OF getting an S lock.
You only get an S lock if you do not intend to modify the data. And if
you don't intend to modify the data, then there's no reason to upgrade
the lock to U or X.

Remember that this whole issue is only relevant if you use a higher than
default isolation level, like repeatable read or serializable (either by
setting the default transaction isolation level or by using locking
hints on the select statement). With normal isolation level, the S lock
is released as soon as the select finishes executing, so you can't get
deadlocks this way. With higher isolation level, S locks will be held
until the end of the transaction.

>If other transaction is holding U lock, then my S lock request will be
>blocked which eliminates the deadlock. It seems the intention of updating
>the row in future plays a key role and the programmer must see that.

Yes. That''s the programmer's responsibility.

>Can
>getting U lock happen without using "lock hints" or necessarily I must use
>them if I intend to perform modifications after doing the select?

You must use locking hints. There is no way that SQL Server kan know if
you might change the data from a SELECT statement later, or if you are
just running your reporting jobs. As far as I know, READ_MIND is not
even on the reserved word list yet <smile>.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

19 Sep 2005 2:10 AM

Brian Selzer

> S means: I need it, but others are free to use it as well.
> X means: I need it, and nobody else can have it.
> U means: I need it, others are free to use it as well, but I do reserve

the right to get an X lock later.

Hugo is exactly right, Leila. I would just add one thing. Don't confuse an
update lock (U) with an intent exclusive (IX) lock. Intent locking deals
with the lock hierarchy, not individual resources. For example, if an
exclusive lock is placed on a row, an intent exclusive lock is placed on the
page and on the table--thus preventing another transaction from locking the
page or the table. An intent lock will only be applied if there are
resources lower in the lock heirarchy that can also be locked. So, in plain
english:

S means: I need it, but others are free to see it but not to change it.
X means: I need it, and nobody else can see it or change it.
U means: I need it, others are free to see it, but I'm the only one who can
change it.

IX means: I need one of the rows on this page, so nobody else can lock this
page exclusively.

Show quote

19 Sep 2005 11:03 PM

Hugo Kornelis

On Sun, 18 Sep 2005 22:10:42 -0400, Brian Selzer wrote:

(snip)

> I would just add one thing. Don't confuse an
>update lock (U) with an intent exclusive (IX) lock.
(...)

Hi Brian,

Thanks for the useful addition!

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

20 Sep 2005 7:44 PM

Leila

Thanks Brian,
Intent locks were something that I mixed with U locks! Do you mean every
transaction who need to modify a row, will have to acquire U lock first and
then try for X?
Leila

Show quote

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:#Xs$X9LvFHA.3100@TK2MSFTNGP12.phx.gbl...
> > S means: I need it, but others are free to use it as well.
> > X means: I need it, and nobody else can have it.
> > U means: I need it, others are free to use it as well, but I do reserve
> the right to get an X lock later.
>
> Hugo is exactly right, Leila. I would just add one thing. Don't confuse
an
> update lock (U) with an intent exclusive (IX) lock. Intent locking deals
> with the lock hierarchy, not individual resources. For example, if an
> exclusive lock is placed on a row, an intent exclusive lock is placed on
the
> page and on the table--thus preventing another transaction from locking
the
> page or the table. An intent lock will only be applied if there are
> resources lower in the lock heirarchy that can also be locked. So, in
plain
> english:
>
> S means: I need it, but others are free to see it but not to change it.
> X means: I need it, and nobody else can see it or change it.
> U means: I need it, others are free to see it, but I'm the only one who
can
> change it.
>
> IX means: I need one of the rows on this page, so nobody else can lock
this
> page exclusively.
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> news:fauoi111gh1pnl10i2a7v3822lmrndamr8@4ax.com...
> > On Sat, 17 Sep 2005 16:28:44 +0430, Leila wrote:
> >
> > >Thanks Brian,
> > >I agree! My logic cannot work because of the lock compatibility issue.
> But I
> > >mean what if my logic was used in SQL Server for lock compatibility?
> >
> > Hi Leila,
> >
> > Well, for starters, any illusion of getting coherent results from the
> > server would be down the drain.
> >
> > An X lock is used when rows are changed, but the change is not yet
> > committed. There is a chance that the chance will be rolled back; if
> > that happens, the changed data has (logically) never been in the
> > database.
> >
> > If an X lock and an S lock are permitted to co-exist, things like the
> > following could happen:
> >
> > Scenario #1:
> > - You perform an UPDATE. An X lock is taken, the data is changed and SQL
> > Server start checking constraints and executing triggers.
> > - In the few milliseconds that takes, I read data from the table. The S
> > lock is granted because of your logic, so I get to see your data.
> > - Your transaction is rolled back, because the trigger detected a
> > violation of a business rule.
> > - But in the meantime, I am looking at your data. I see that the
> > business rule is violated, so I call the DBA, telling him that the
> > database is corrupt. He immediately brings down the server and starts
> > investigating. Of course, he can't find any corrupt data. In the end,
> > you, me and 50 colleagues have been shut out from the system for over an
> > hour, the DBA is pissed and I am fired for bothering the DBA with
> > poblems that don't really exist.
> >
> > Scenario #2:
> > - You start a long transaction, that first reads some data. You'll need
> > to read the same data again later in the transaction, and it's critical
> > that the data is unchanged, so you set the transacion isolation level to
> > repeatable read. The S lock is granted, and is not released after the
> > SELECT statement finishes.
> > - In the mean time, I do an UPDATE. The X lock I request is granted
> > because of your logic; the data is changed and my transaction is
> > successfully committed.
> > - After a few minutes, your long-running transaction re-reads the data.
> > It has now changed. Read repeateable apparently isn't.
> >
> > Scenario #3:
> > - I do a SELECT that is executed using an index seek followed by a
> > bookmark lookup.
> > - At the same time, yoou start a DELETE that will delete some of the
> > rows in the table. Because your X lock and my S lock are allowed to
> > co-exist, the queries are running concurrently.
> > - If the timing is exact right, SQL Server might find an index entry
> > that matches the search arguments in my query, do a bookmark lookup,
> > then find nothing because the row has just been deleted. BLAM. Internal
> > error, and we end up calling Microsoft for support.
> >
> > These and other doom scenario's are the reason that your logic can't be
> > used - an exclusive lock really needs to exclude all other locks (as the
> > name already suggests).
> >
> > > Why U lock exists?
> >
> > Because S and X locks can't co-exist. And with just S and X lock,
> > there's a good chance to get deadlocks (as I explained in my previous
> > message), that can be prevented by the U lock.
> >
> > In plain English:
> > S means: I need it, but others are free to use it as well.
> > X means: I need it, and nobody else can have it.
> > U means: I need it, others are free to use it as well, but I do reserve
> > the right to get an X lock later.
> >
> > Best, Hugo
> > --
> >
> > (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>

20 Sep 2005 8:13 PM

Brian Selzer

No. A simple UPDATE will only require an X lock. The problem comes in when
the TRANSACTION ISOLATION LEVEL is REPEATABLE READ or SERIALIZABLE, and you
issue a SELECT for a row prior to issuing an UPDATE of that same row within
the same transaction. Only then does it make sense to specify
WITH(UPDLOCK). If the TRANSACTION ISOLATION LEVEL is READ COMMITTED or READ
UNCOMMITTED, then the shared lock will be released after the SELECT
completes, so it won't cause a deadlock. Another transaction could change
the row between the SELECT and the UPDATE, however, so you must either be
willing to accept that or to use some other mechanism--such as
rowversioning--to detect and deal with that situation should it arise.

Show quote

"Leila" <Lei***@hotpop.com> wrote in message
news:OkNvNvhvFHA.1392@tk2msftngp13.phx.gbl...
> Thanks Brian,
> Intent locks were something that I mixed with U locks! Do you mean every
> transaction who need to modify a row, will have to acquire U lock first
> and
> then try for X?
> Leila
>
>
>
> "Brian Selzer" <br***@selzer-software.com> wrote in message
> news:#Xs$X9LvFHA.3100@TK2MSFTNGP12.phx.gbl...
>> > S means: I need it, but others are free to use it as well.
>> > X means: I need it, and nobody else can have it.
>> > U means: I need it, others are free to use it as well, but I do reserve
>> the right to get an X lock later.
>>
>> Hugo is exactly right, Leila. I would just add one thing. Don't confuse
> an
>> update lock (U) with an intent exclusive (IX) lock. Intent locking deals
>> with the lock hierarchy, not individual resources. For example, if an
>> exclusive lock is placed on a row, an intent exclusive lock is placed on
> the
>> page and on the table--thus preventing another transaction from locking
> the
>> page or the table. An intent lock will only be applied if there are
>> resources lower in the lock heirarchy that can also be locked. So, in
> plain
>> english:
>>
>> S means: I need it, but others are free to see it but not to change it.
>> X means: I need it, and nobody else can see it or change it.
>> U means: I need it, others are free to see it, but I'm the only one who
> can
>> change it.
>>
>> IX means: I need one of the rows on this page, so nobody else can lock
> this
>> page exclusively.
>>
>> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
>> news:fauoi111gh1pnl10i2a7v3822lmrndamr8@4ax.com...
>> > On Sat, 17 Sep 2005 16:28:44 +0430, Leila wrote:
>> >
>> > >Thanks Brian,
>> > >I agree! My logic cannot work because of the lock compatibility issue.
>> But I
>> > >mean what if my logic was used in SQL Server for lock compatibility?
>> >
>> > Hi Leila,
>> >
>> > Well, for starters, any illusion of getting coherent results from the
>> > server would be down the drain.
>> >
>> > An X lock is used when rows are changed, but the change is not yet
>> > committed. There is a chance that the chance will be rolled back; if
>> > that happens, the changed data has (logically) never been in the
>> > database.
>> >
>> > If an X lock and an S lock are permitted to co-exist, things like the
>> > following could happen:
>> >
>> > Scenario #1:
>> > - You perform an UPDATE. An X lock is taken, the data is changed and
>> > SQL
>> > Server start checking constraints and executing triggers.
>> > - In the few milliseconds that takes, I read data from the table. The S
>> > lock is granted because of your logic, so I get to see your data.
>> > - Your transaction is rolled back, because the trigger detected a
>> > violation of a business rule.
>> > - But in the meantime, I am looking at your data. I see that the
>> > business rule is violated, so I call the DBA, telling him that the
>> > database is corrupt. He immediately brings down the server and starts
>> > investigating. Of course, he can't find any corrupt data. In the end,
>> > you, me and 50 colleagues have been shut out from the system for over
>> > an
>> > hour, the DBA is pissed and I am fired for bothering the DBA with
>> > poblems that don't really exist.
>> >
>> > Scenario #2:
>> > - You start a long transaction, that first reads some data. You'll need
>> > to read the same data again later in the transaction, and it's critical
>> > that the data is unchanged, so you set the transacion isolation level
>> > to
>> > repeatable read. The S lock is granted, and is not released after the
>> > SELECT statement finishes.
>> > - In the mean time, I do an UPDATE. The X lock I request is granted
>> > because of your logic; the data is changed and my transaction is
>> > successfully committed.
>> > - After a few minutes, your long-running transaction re-reads the data.
>> > It has now changed. Read repeateable apparently isn't.
>> >
>> > Scenario #3:
>> > - I do a SELECT that is executed using an index seek followed by a
>> > bookmark lookup.
>> > - At the same time, yoou start a DELETE that will delete some of the
>> > rows in the table. Because your X lock and my S lock are allowed to
>> > co-exist, the queries are running concurrently.
>> > - If the timing is exact right, SQL Server might find an index entry
>> > that matches the search arguments in my query, do a bookmark lookup,
>> > then find nothing because the row has just been deleted. BLAM. Internal
>> > error, and we end up calling Microsoft for support.
>> >
>> > These and other doom scenario's are the reason that your logic can't be
>> > used - an exclusive lock really needs to exclude all other locks (as
>> > the
>> > name already suggests).
>> >
>> > > Why U lock exists?
>> >
>> > Because S and X locks can't co-exist. And with just S and X lock,
>> > there's a good chance to get deadlocks (as I explained in my previous
>> > message), that can be prevented by the U lock.
>> >
>> > In plain English:
>> > S means: I need it, but others are free to use it as well.
>> > X means: I need it, and nobody else can have it.
>> > U means: I need it, others are free to use it as well, but I do reserve
>> > the right to get an X lock later.
>> >
>> > Best, Hugo
>> > --
>> >
>> > (Remove _NO_ and _SPAM_ to get my e-mail address)
>>
>>
>
>
>