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, ChrisREvery 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
> 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?[vbcol=seagreen]
>|||"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.

>
> "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[vbcol=seagreen]
> a
> scenarios
> transaction?
>sql

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?
>

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, ChrisREvery 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
> 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
> >
> >
>|||"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.
>
> "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
> > 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
> > >
> > >
> >
> >
>

Deadlock in SQL 2000

Hi
I think you don't need wrap the script BEGIN TRAN ...COMMIT because the tran
asction is atomic by itself
"Hongbo" <hongbo@.goodoffices.com> wrote in message news:ezJRDs6lFHA.1372@.TK2
MSFTNGP10.phx.gbl...
Hi,
I have 2 update statements in SQL Server 2000:
1. with TRAN
==
BEGIN TRAN UpdateProductShipFlag
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
COMMIT TRAN UpdateProductShipFlag
==
2. without TRAN
==
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
==
I got "deadlock" error with first statement.
I do not use locking hints in any of my SQL statements.
Would you please tell me the difference between the above 2 statements
on lock level?
Thank you
HBAlso, it takes 2 SPIDs to deadlock. Where's the other SPID? What T-SQL
is the other SPID executing? What locks are involved in the deadlock?
etc. etc. Lots of questions that need to be asked to resolve your issue.
But to answer your question, there is no difference between those 2
statements on a lock level - as Uri said the update statement is atomic
on it's own so the scope of any locks involved in that statement are the
same with or without the explicit BEGIN/COMMIT and the BEING/COMMIT pair
don't change the type of locks involved or the resources locked.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Uri Dimant wrote:

> Hi
> I think you don't need wrap the script BEGIN TRAN ...COMMIT because
> the tranasction is atomic by itself
>
> "Hongbo" <hongbo@.goodoffices.com <mailto:hongbo@.goodoffices.com>>
> wrote in message news:ezJRDs6lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have 2 update statements in SQL Server 2000:
> 1. with TRAN
> ==
> BEGIN TRAN UpdateProductShipFlag
> UPDATE ProductToOrder
> SET ShippedFlag=1, ShippedDate=getdate()
> WHERE ProductToOrderId=500603
> COMMIT TRAN UpdateProductShipFlag
> ==
> 2. without TRAN
> ==
> UPDATE ProductToOrder
> SET ShippedFlag=1, ShippedDate=getdate()
> WHERE ProductToOrderId=500603
> ==
> I got "deadlock" error with first statement.
> I do not use locking hints in any of my SQL statements.
> Would you please tell me the difference between the above 2 statements
> on lock level?
> Thank you
> HB
>|||Uri and Mike,
Thank you for the help!
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uA0
26l$lFHA.4056@.TK2MSFTNGP10.phx.gbl...
Also, it takes 2 SPIDs to deadlock. Where's the other SPID? What T-SQL is
the other SPID executing? What locks are involved in the deadlock? etc. et
c. Lots of questions that need to be asked to resolve your issue.
But to answer your question, there is no difference between those 2 statemen
ts on a lock level - as Uri said the update statement is atomic on it's own
so the scope of any locks involved in that statement are the same with or wi
thout the explicit BEGIN/COMMIT and the BEING/COMMIT pair don't change the t
ype of locks involved or the resources locked.
mike hodgson
blog: http://sqlnerd.blogspot.com
Uri Dimant wrote:
Hi
I think you don't need wrap the script BEGIN TRAN ...COMMIT because the tra
nasction is atomic by itself
"Hongbo" <hongbo@.goodoffices.com> wrote in message news:ezJRDs6lFHA.1372@.TK2
MSFTNGP10.phx.gbl...
Hi,
I have 2 update statements in SQL Server 2000:
1. with TRAN
==
BEGIN TRAN UpdateProductShipFlag
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
COMMIT TRAN UpdateProductShipFlag
==
2. without TRAN
==
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
==
I got "deadlock" error with first statement.
I do not use locking hints in any of my SQL statements.
Would you please tell me the difference between the above 2 statements
on lock level?
Thank you
HB

Dead Lock

Hello !!!

I have 2 transactions, the first one has MANY updates to the table A and it finishes with a commit or rollback (ONLY AT THE END), the second one has only one insert into the table A that finishes with a commit or rollback, the problem is that the update process takes a long time to finish, and the insert process could be thrown during the first process, there's where I get everything locked cause the table A is locked and my java aplication gets stuck.

Note: When I execute each transaction independient I have no problems.

Is there any possibility to lock table A completly for the first transaction and release It for second one ??

Could you give me any suggestion of what to do step by step ?

Thanks !!!Are you running these processes by submitting SQL commands from your Java App, or are you just executing existing Stored Procedures?|||begin tran
select 1 from tableA (tablock) where 1=2
update...
if @.@.error != 0 begin
...
rollback tran
return (1)
end
...
commit tran