A question about Deadlock.

Hi All,
I read a book which has the following text:
"DEADLOCK - Although locking solves one problem, it introduces
another. Consider what might happen when two users
want to order two items from inventory. Suppose User A
wants to order some paper, and if she can get the paper,
she wants to order some pencils. Then suppose User B
wants to order some pencils, and if he can get the pencils,
he wants to order some paper.
1. Lock paper for user A.
2. Lock pencils for user B.
3. Process A's requests; write paper record.
4. Process B's requests; write pencil record.
5. Put A in wait state for pencils.
6. Put B in wait state for paper.
** Locked ** "
My question is: Could A releases the lock on paper after he is done with
step 3 and
B releases the lock on pencil after he is done with step 4? In this way, A
and B will not have a deadlock.
It seems that the locks have to be in place during the entire transaction
and causing the deadlock.
Thank you a lot.
ChrisDeadlocks occur when two transactions are trying to access a resource the
other has locked. They don't happen all the time, it depends on the design
of the queries and the program. What happens a lot to is actually blocks,
where one transaction blocks other ones because it is using a resource.
One thing for example that controls this is the size of the transaction.
The transaction holds a lock until it is completed and then committed. The
longer the transaction runs, the higher the likelihood a blocking or
deadlock situation will occur. To prevent issues like this, query design,
index design and table design all come into play. Denormalizing a table so
it contains data about multiple items (paper and pencils) to cut down on
joins helps.
When a deadlock occurs, SQL will choose a loser transaction and disconnect
it.
--
*************************************
Andy S.
andy_mcdba@.yahoo.com
*************************************
"yma" <yma@.pacbell.net> wrote in message
news:e0wTDWjeDHA.1836@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I read a book which has the following text:
> "DEADLOCK - Although locking solves one problem, it introduces
> another. Consider what might happen when two users
> want to order two items from inventory. Suppose User A
> wants to order some paper, and if she can get the paper,
> she wants to order some pencils. Then suppose User B
> wants to order some pencils, and if he can get the pencils,
> he wants to order some paper.
> 1. Lock paper for user A.
> 2. Lock pencils for user B.
> 3. Process A's requests; write paper record.
> 4. Process B's requests; write pencil record.
> 5. Put A in wait state for pencils.
> 6. Put B in wait state for paper.
> ** Locked ** "
> My question is: Could A releases the lock on paper after he is done with
> step 3 and
> B releases the lock on pencil after he is done with step 4? In this way,
A
> and B will not have a deadlock.
> It seems that the locks have to be in place during the entire transaction
> and causing the deadlock.
> Thank you a lot.
> Chris
>|||> It seems that the locks have to be in place during the entire
transaction
> and causing the deadlock.
This is true. If locks were released before the transaction was
committed, it would not be possible to rollback the transaction and data
integrity would be lost.
Note that SQL Server will choose a deadlock victim in the scenario you
describe so one of the transactions will continue; the deadlock will not
be indefinite.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"yma" <yma@.pacbell.net> wrote in message
news:e0wTDWjeDHA.1836@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I read a book which has the following text:
> "DEADLOCK - Although locking solves one problem, it introduces
> another. Consider what might happen when two users
> want to order two items from inventory. Suppose User A
> wants to order some paper, and if she can get the paper,
> she wants to order some pencils. Then suppose User B
> wants to order some pencils, and if he can get the pencils,
> he wants to order some paper.
> 1. Lock paper for user A.
> 2. Lock pencils for user B.
> 3. Process A's requests; write paper record.
> 4. Process B's requests; write pencil record.
> 5. Put A in wait state for pencils.
> 6. Put B in wait state for paper.
> ** Locked ** "
> My question is: Could A releases the lock on paper after he is done
with
> step 3 and
> B releases the lock on pencil after he is done with step 4? In this
way, A
> and B will not have a deadlock.
> It seems that the locks have to be in place during the entire
transaction
> and causing the deadlock.
> Thank you a lot.
> Chris
>|||Transactions are all about achieving correct results, and correct results
depend on the needs of your business logic. The example given below is a
simplified one for demonstration purposes and it is thus easy to find a
workaround such as splitting it into two transactions. However, that split
may not represent the actual business requirement. Enhance the description
just slightly and you'll see this. Say that the users only want to place
the order if they can get all of the items. Now it is clear that you can't
split the transaction into two because doing so might result in a user
getting paper but no pencils, or vice versa.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"yma" <yma@.pacbell.net> wrote in message
news:e0wTDWjeDHA.1836@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I read a book which has the following text:
> "DEADLOCK - Although locking solves one problem, it introduces
> another. Consider what might happen when two users
> want to order two items from inventory. Suppose User A
> wants to order some paper, and if she can get the paper,
> she wants to order some pencils. Then suppose User B
> wants to order some pencils, and if he can get the pencils,
> he wants to order some paper.
> 1. Lock paper for user A.
> 2. Lock pencils for user B.
> 3. Process A's requests; write paper record.
> 4. Process B's requests; write pencil record.
> 5. Put A in wait state for pencils.
> 6. Put B in wait state for paper.
> ** Locked ** "
> My question is: Could A releases the lock on paper after he is done with
> step 3 and
> B releases the lock on pencil after he is done with step 4? In this way,
A
> and B will not have a deadlock.
> It seems that the locks have to be in place during the entire transaction
> and causing the deadlock.
> Thank you a lot.
> Chris
>