Re: deadlock questions
From: Rick Sawtell (quickening_at_msn.com)
Date: 11/02/04
- Next message: mkelly: "Cannot insert into partitioned view"
- Previous message: AnthonyThomas: "Re: set nocount on"
- In reply to: ChrisR: "deadlock questions"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 2 Nov 2004 12:30:36 -0600
Chris,
Deadlocks occur when two procedures are trying to work with each other's
locked resources. In these situations, a deadlock victim is chosen and then
given an error. Their resources are then released so that the winner of the
deadlock can continue processing.
Transactions are not the only way to have a deadlock occur, but they are
more likely to occur during a transaction because transactions will maintain
locks until the transaction completes (COMMIT or ROLLBACK).
Large queries that are not transacted could possibly create a deadlock issue
as well. For example, if you are running a very large update on a table,
locking will begin and progress as follows:
1. Acquire row locks.
2. If enough row locks are acquired, then acquire page locks
3. If enough page locks are acquired, then acquire extent locks
4. If enough extent locks are acquired, then acquire a table lock.
This is known as lock escalation. So a large update that hits most rows in
a large table may eventually acquire a table level lock. If another data
modification is running on the same table and has some row locks or extent
locks and it is also attempting to acquire a table level lock, then one of
the two process will likely end as a deadlock victim.
To avoid these types of situations, it is best to write your procedures to
minimize the time a transaction runs. You should also try to use resources
(tables, views etc.) within your transactions and procedures in the same
order. If table A is already locked by a procedure, then a second procedure
must wait to acquire locks. This means that the second procedure is waiting
rather than becoming a potential deadlock victim.
Other options you may look at with large table updates is applying a query
hint that acquires a table lock right out of the gate rather than waiting
for lock escalation to take place.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@NoEmails.com> wrote in message
news:%23xb1mVQwEHA.1264@TK2MSFTNGP12.phx.gbl...
> Im able to produce a deadlock for learning purposes with BEGIN TRANSACTION
> and no corresponding COMMIT. I do this using the textbook definition of a
> deadlock as modeled in BOL. Easy enough. But what are the other scenarios
> that can cause deadlocks that arent in that textbook definition?
> Speifically, do UPDATES like these always need to be inside a transaction?
>
> --
> sql2k sp3
>
> TIA, ChrisR
>
>
- Next message: mkelly: "Cannot insert into partitioned view"
- Previous message: AnthonyThomas: "Re: set nocount on"
- In reply to: ChrisR: "deadlock questions"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
