Can several UPDATE statements deadlock within serializable transaction

This can cause conversion deadlock:
====
set transaction isolation level serializable
begin tran
select * from authors where au_id = 'bla'
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
because shared locks in serializable transactions are held for the duration
of the transaction and exculsive locks are not compatible with shared locks
from another transaction.
Here is the question - can this deadlock as well?
====
set transaction isolation level serializable
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
If this can deadlock, how can I prevent it?
I am trying to resolve COM+ deadlocking issues...
Thanks,
-StanWhy are you using a SERIALIZABLE level?
In the update, could you get away with specifying
an UPDLOCK hint instead?
"Stan" <nospam@.yahoo.com> wrote in message
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====
> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> because shared locks in serializable transactions are held for the
duration
> of the transaction and exculsive locks are not compatible with shared
locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====
> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||1. I am not using serializable, COM+ is
2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using a SERIALIZABLE level?
> In the update, could you get away with specifying
> an UPDLOCK hint instead?
> "Stan" <nospam@.yahoo.com> wrote in message
> news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> duration
> locks
>|||2. UPDATE hint should be put on select statement
Thank you,
Alex
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
>|||Use the hint on the SELECT
ie
SELECT mycolumn
FROM mytable WITH (UPDLOCK)
WHERE ID = @.ID
I don't know anything about COM+ so I couldn't
say how to suppress the SERIALIZABLE... it
seems too drastic.
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
>|||Hi Stan,
I've been reading a lot about locking and transaction isolation level
lately, as I was troubleshooting deadlocks from dll's running under COM+ as
well. I don't think a single update statement can cause deadlocks. Is there
a select statement in the calling client code that runs within the same
transaction? In C# code - which I was reviewing - I had to look for methods
with the attribute "Autocomplete()" within classes with the attribute
"Transaction(TransactionOption.Required)". This meant that the execution of
this method will be encapsulated in 1 transaction. If no unhandled exception
occurs, the transaction is automatically committed and otherwise it is
rolled back. Which leads me to the question if the T-SQL really has a
"commit" statement, because it isn't needed and could maybe even cause an
error (I'm not sure how COM+ reacts to a "no current transaction available"
when it tries to commit after the transaction is already closed, it may or
may not check the @.@.trancount function.
This is al speculative, now some solid advice: execute the following
statement in query analyzer as sa user: "dbcc traceon(-1, 1204)". 1204
instructs Sql Server to log deadlock information to the error log file - you
can find it under Management in the enterprise manager -; -1 makes the
traceflag global instead of limited to the current session. If you search in
google with "deadlock 1204" you'll learn how to interpret the error log.
This was a great help for me when investigating the deadlock situations.
Cheers,
Henk Kok
"Stan" <nospam@.yahoo.com> schreef in bericht
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====
> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> because shared locks in serializable transactions are held for the
> duration
> of the transaction and exculsive locks are not compatible with shared
> locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====
> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||> transaction? In C# code - which I was reviewing - I had to look for
methods
> with the attribute "Autocomplete()" within classes with the attribute
> "Transaction(TransactionOption.Required)". This meant that the execution
of
> this method will be encapsulated in 1 transaction.
I understand that. However all my "get" stored procedures have
"set transaction isolation level read uncommitted" statement. This should
removed the shared locks immidiately after select statement, but I still
have deadlocks...
"Update" stored procedures do not have this statement and I was wondering if
this can be a cause of deadlocking..

Can several UPDATE statements deadlock within serializable transaction

This can cause conversion deadlock:
==== set transaction isolation level serializable
begin tran
select * from authors where au_id = 'bla'
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
because shared locks in serializable transactions are held for the duration
of the transaction and exculsive locks are not compatible with shared locks
from another transaction.
Here is the question - can this deadlock as well?
==== set transaction isolation level serializable
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
If this can deadlock, how can I prevent it?
I am trying to resolve COM+ deadlocking issues...
Thanks,
-StanWhy are you using a SERIALIZABLE level?
In the update, could you get away with specifying
an UPDLOCK hint instead?
"Stan" <nospam@.yahoo.com> wrote in message
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
duration
> of the transaction and exculsive locks are not compatible with shared
locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||1. I am not using serializable, COM+ is
2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using a SERIALIZABLE level?
> In the update, could you get away with specifying
> an UPDLOCK hint instead?
> "Stan" <nospam@.yahoo.com> wrote in message
> news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > This can cause conversion deadlock:
> >
> > ====> > set transaction isolation level serializable
> >
> > begin tran
> >
> > select * from authors where au_id = 'bla'
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > because shared locks in serializable transactions are held for the
> duration
> > of the transaction and exculsive locks are not compatible with shared
> locks
> > from another transaction.
> >
> > Here is the question - can this deadlock as well?
> >
> > ====> > set transaction isolation level serializable
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > If this can deadlock, how can I prevent it?
> >
> > I am trying to resolve COM+ deadlocking issues...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||2. UPDATE hint should be put on select statement
--
Thank you,
Alex
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Use the hint on the SELECT
ie
SELECT mycolumn
FROM mytable WITH (UPDLOCK)
WHERE ID = @.ID
I don't know anything about COM+ so I couldn't
say how to suppress the SERIALIZABLE... it
seems too drastic.
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Hi Stan,
I've been reading a lot about locking and transaction isolation level
lately, as I was troubleshooting deadlocks from dll's running under COM+ as
well. I don't think a single update statement can cause deadlocks. Is there
a select statement in the calling client code that runs within the same
transaction? In C# code - which I was reviewing - I had to look for methods
with the attribute "Autocomplete()" within classes with the attribute
"Transaction(TransactionOption.Required)". This meant that the execution of
this method will be encapsulated in 1 transaction. If no unhandled exception
occurs, the transaction is automatically committed and otherwise it is
rolled back. Which leads me to the question if the T-SQL really has a
"commit" statement, because it isn't needed and could maybe even cause an
error (I'm not sure how COM+ reacts to a "no current transaction available"
when it tries to commit after the transaction is already closed, it may or
may not check the @.@.trancount function.
This is al speculative, now some solid advice: execute the following
statement in query analyzer as sa user: "dbcc traceon(-1, 1204)". 1204
instructs Sql Server to log deadlock information to the error log file - you
can find it under Management in the enterprise manager -; -1 makes the
traceflag global instead of limited to the current session. If you search in
google with "deadlock 1204" you'll learn how to interpret the error log.
This was a great help for me when investigating the deadlock situations.
Cheers,
Henk Kok
"Stan" <nospam@.yahoo.com> schreef in bericht
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
> duration
> of the transaction and exculsive locks are not compatible with shared
> locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||> transaction? In C# code - which I was reviewing - I had to look for
methods
> with the attribute "Autocomplete()" within classes with the attribute
> "Transaction(TransactionOption.Required)". This meant that the execution
of
> this method will be encapsulated in 1 transaction.
I understand that. However all my "get" stored procedures have
"set transaction isolation level read uncommitted" statement. This should
removed the shared locks immidiately after select statement, but I still
have deadlocks...
"Update" stored procedures do not have this statement and I was wondering if
this can be a cause of deadlocking..