deadlock questions

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
Every DML statement creates an implicit transaction; this allows for
transactional atomicity. For instance, if an UPDATE effects 3 rows and only
one has been updated at the time of a server crash, the server will be able
to roll back the transaction upon restart. So you will always have a
transaction, whether or not you explicitly create one. You should create
transactions when you are doing more than one type of manipulation in a
batch, in which any failure within the batch would mean the whole thing was
invalid. For instance, if you were to insert an order into an orders table,
and then insert line items into a line items table, if one of the line items
were to fail, the entire order might not be in a consistent state.
Therefore, you would want the entire operation in a single transaction.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"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
>
|||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
>
|||So then a deadlock occur with using the BEGIN TRANS?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Every DML statement creates an implicit transaction; this allows for
> transactional atomicity. For instance, if an UPDATE effects 3 rows and
only
> one has been updated at the time of a server crash, the server will be
able
> to roll back the transaction upon restart. So you will always have a
> transaction, whether or not you explicitly create one. You should create
> transactions when you are doing more than one type of manipulation in a
> batch, in which any failure within the batch would mean the whole thing
was
> invalid. For instance, if you were to insert an order into an orders
table,
> and then insert line items into a line items table, if one of the line
items[vbcol=seagreen]
> were to fail, the entire order might not be in a consistent state.
> Therefore, you would want the entire operation in a single transaction.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "ChrisR" <ChrisR@.NoEmails.com> wrote in message
> news:%23xb1mVQwEHA.1264@.TK2MSFTNGP12.phx.gbl...
TRANSACTION[vbcol=seagreen]
a[vbcol=seagreen]
scenarios[vbcol=seagreen]
transaction?
>
|||"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:erQLnqQwEHA.3528@.tk2msftngp13.phx.gbl...
> So then a deadlock occur with using the BEGIN TRANS?
Not necessarily.
The simplest answer (which of course is also the most complex) is keep your
transactions as short as possible.
[vbcol=seagreen]
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uM8XsfQwEHA.2624@.TK2MSFTNGP11.phx.gbl...
> only
> able
create[vbcol=seagreen]
> was
> table,
> items
> TRANSACTION
of
> a
> scenarios
> transaction?
>