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