transaction isolation level setting serializable - deadlocks

transaction isolation level setting serializable - deadlocks

Post by Uday Eyunn » Thu, 06 Feb 2003 06:48:52



Hello -

I am using transaction isolation level serializable in one of the stored
procedure and consistently getting deadlock if I have to more than three
application instances running pointing to the same database. The stored
procedure that I am executing is a simple one. It tries to check whether the
record exists in the table; if exists update the value in one column
otherwise inserts the record in the table. I can't the use the default
isolation level of read committed because it generate duplicate key errors.
(which I know why). Can anyone look the skeleton stored procedure below and
point out why it generates deadlocks? The stored procedure is operating on
only one table.

create procedure
...
begin
    set no count on
    set transaction isolation level serializable
    begin tran

        select col1 from tableA where <conditionA>

        beign
            insert <record> into tableA
        end
        else
        begin
            update tableA set col1 = col1 + <some value> where <conditionA>
        end

    commit tran
    set transation isolation level read committed  -- revert back to default
setting
    set nocount off
end

thanks
uday

 
 
 

transaction isolation level setting serializable - deadlocks

Post by Nigel Rivet » Thu, 06 Feb 2003 07:06:49


Both threads will take a lock on the table to service the
select and hold it due to the isolation level. Nether can
then upgrade the lock to do the insert/update so one will
deadlock.

You only have problems with insert as the update will go
through anyway (interested to know how you decide which
update should go first and potentially be lost.

Easy way out of this but taking exclusive lock.
begin tran
 update tableA (holdlock, tablockx)
set col1 = col1 + <some value> where <conditionA>

insert <record> into tableA
commit tran

Quote:>-----Original Message-----
>Hello -

>I am using transaction isolation level serializable in
one of the stored
>procedure and consistently getting deadlock if I have to
more than three
>application instances running pointing to the same

database. The stored
Quote:>procedure that I am executing is a simple one. It tries

to check whether the
Quote:>record exists in the table; if exists update the value in
one column
>otherwise inserts the record in the table. I can't the
use the default
>isolation level of read committed because it generate

duplicate key errors.
Quote:>(which I know why). Can anyone look the skeleton stored
procedure below and
>point out why it generates deadlocks? The stored

procedure is operating on
>only one table.

>create procedure
>....
>begin
>    set no count on
>    set transaction isolation level serializable
>    begin tran

>        select col1 from tableA where <conditionA>

>        beign
>            insert <record> into tableA
>        end
>        else
>        begin
>            update tableA set col1 = col1 + <some value>
where <conditionA>
>        end

>    commit tran
>    set transation isolation level read committed  --

revert back to default

- Show quoted text -

Quote:>setting
>    set nocount off
>end

>thanks
>uday

>.


 
 
 

transaction isolation level setting serializable - deadlocks

Post by Alejandro Mes » Thu, 06 Feb 2003 07:27:59



create procedure
...
begin
    set no count on
    set transaction isolation level serializable
    begin tran

    if exists (select * from tableA where <conditionA>)
           update tableA set col1 = col1 + <some value> where <conditionA>
    else
            insert <record> into tableA


       commit tran
end

AMB

 
 
 

transaction isolation level setting serializable - deadlocks

Post by Uday Eyunn » Thu, 06 Feb 2003 07:47:14


I thought by using transaction isolation level serializable i wouldn't have
two threads trying to run the update. I didn't see an update being lost till
now. Table lock is a overkill. So, I am trying to see whether I can solve
the deadlock without using tablockx. The application can run for a day to a
week. though a table lock would be held for a micro sec or so during the
update call, i am afraid it will create some other problems.

When I wrote the store proc intially I had only update and insert statement
with in the transaction. Run the update first and if the row count is 0 the
stored proc inserts a record. That wasn't working and generating duplicate
key errors. The documentation on transaction isolation level talks about
select statements. So, I thought I would start the stored proc with a select
statement to acquire a hold lock and do an update/insert depending on result
of select statement.


> Both threads will take a lock on the table to service the
> select and hold it due to the isolation level. Nether can
> then upgrade the lock to do the insert/update so one will
> deadlock.

> You only have problems with insert as the update will go
> through anyway (interested to know how you decide which
> update should go first and potentially be lost.

> Easy way out of this but taking exclusive lock.
> begin tran
>  update tableA (holdlock, tablockx)
> set col1 = col1 + <some value> where <conditionA>

> insert <record> into tableA
> commit tran

> >-----Original Message-----
> >Hello -

> >I am using transaction isolation level serializable in
> one of the stored
> >procedure and consistently getting deadlock if I have to
> more than three
> >application instances running pointing to the same
> database. The stored
> >procedure that I am executing is a simple one. It tries
> to check whether the
> >record exists in the table; if exists update the value in
> one column
> >otherwise inserts the record in the table. I can't the
> use the default
> >isolation level of read committed because it generate
> duplicate key errors.
> >(which I know why). Can anyone look the skeleton stored
> procedure below and
> >point out why it generates deadlocks? The stored
> procedure is operating on
> >only one table.

> >create procedure
> >....
> >begin
> >    set no count on
> >    set transaction isolation level serializable
> >    begin tran

> >        select col1 from tableA where <conditionA>

> >        beign
> >            insert <record> into tableA
> >        end
> >        else
> >        begin
> >            update tableA set col1 = col1 + <some value>
> where <conditionA>
> >        end

> >    commit tran
> >    set transation isolation level read committed  --
> revert back to default
> >setting
> >    set nocount off
> >end

> >thanks
> >uday

> >.

 
 
 

transaction isolation level setting serializable - deadlocks

Post by David Brown » Thu, 06 Feb 2003 09:15:22


You want a tablock.
You need a tablock.
In order to run correctly this stored procedure must be serialized.
Quote:> though a table lock would be held for a micro sec

end of discussion.  Take the exclusive tablock.
(you can just add the TABLOCKX hint to your select)

BTW this is exactly the reason IDENTITY columns were invented.
And why ORACLE sequences were invented.

It's not for convenience; its because there is no way in ANSI SQL to
implement an autonumber column without serializing all of the inserts into
the table.

David


> I thought by using transaction isolation level serializable i wouldn't
have
> two threads trying to run the update. I didn't see an update being lost
till
> now. Table lock is a overkill. So, I am trying to see whether I can solve
> the deadlock without using tablockx. The application can run for a day to
a
> week. though a table lock would be held for a micro sec or so during the
> update call, i am afraid it will create some other problems.

> When I wrote the store proc intially I had only update and insert
statement
> with in the transaction. Run the update first and if the row count is 0
the
> stored proc inserts a record. That wasn't working and generating duplicate
> key errors. The documentation on transaction isolation level talks about
> select statements. So, I thought I would start the stored proc with a
select
> statement to acquire a hold lock and do an update/insert depending on
result
> of select statement.


> > Both threads will take a lock on the table to service the
> > select and hold it due to the isolation level. Nether can
> > then upgrade the lock to do the insert/update so one will
> > deadlock.

> > You only have problems with insert as the update will go
> > through anyway (interested to know how you decide which
> > update should go first and potentially be lost.

> > Easy way out of this but taking exclusive lock.
> > begin tran
> >  update tableA (holdlock, tablockx)
> > set col1 = col1 + <some value> where <conditionA>

> > insert <record> into tableA
> > commit tran

> > >-----Original Message-----
> > >Hello -

> > >I am using transaction isolation level serializable in
> > one of the stored
> > >procedure and consistently getting deadlock if I have to
> > more than three
> > >application instances running pointing to the same
> > database. The stored
> > >procedure that I am executing is a simple one. It tries
> > to check whether the
> > >record exists in the table; if exists update the value in
> > one column
> > >otherwise inserts the record in the table. I can't the
> > use the default
> > >isolation level of read committed because it generate
> > duplicate key errors.
> > >(which I know why). Can anyone look the skeleton stored
> > procedure below and
> > >point out why it generates deadlocks? The stored
> > procedure is operating on
> > >only one table.

> > >create procedure
> > >....
> > >begin
> > >    set no count on
> > >    set transaction isolation level serializable
> > >    begin tran

> > >        select col1 from tableA where <conditionA>

> > >        beign
> > >            insert <record> into tableA
> > >        end
> > >        else
> > >        begin
> > >            update tableA set col1 = col1 + <some value>
> > where <conditionA>
> > >        end

> > >    commit tran
> > >    set transation isolation level read committed  --
> > revert back to default
> > >setting
> > >    set nocount off
> > >end

> > >thanks
> > >uday

> > >.

 
 
 

transaction isolation level setting serializable - deadlocks

Post by Uday Eyunn » Fri, 07 Feb 2003 06:27:41


Can anyone tell why would the stored procedure A runs faster (almost twice
or three times faster) than stored procedure B. The result is the same for
both the stored procs. I thought the stored procedure B would run faster
because 99% of the time update statement gets executed and 1% of the time
insert gets executed (in the application I am working). So, there is only
one statement to execute inside a transaction in the stored procedure B but
there are two statements to execute (select and update/insert) in the stored
procedure A. I am using SQL 2000.

Stored Proc A
---------------

Create Procedure
begin
    begin tran


        -- insert a new record
        insert tableA <record>
    else
        -- update an existing record in the table
        update tableA set field = field + something where <conditionA>
    endif
    end tran
end

Stored Proc B
--------------
create procdure
begin
    begin tran
    update table with (TABLOCX) set field = field + something where
<conditionA>

        -- update failed; insert a record
        insert tableA <record>
    end if
    end tran
end



> You want a tablock.
> You need a tablock.
> In order to run correctly this stored procedure must be serialized.
> > though a table lock would be held for a micro sec
> end of discussion.  Take the exclusive tablock.
> (you can just add the TABLOCKX hint to your select)

> BTW this is exactly the reason IDENTITY columns were invented.
> And why ORACLE sequences were invented.

> It's not for convenience; its because there is no way in ANSI SQL to
> implement an autonumber column without serializing all of the inserts into
> the table.

> David



> > I thought by using transaction isolation level serializable i wouldn't
> have
> > two threads trying to run the update. I didn't see an update being lost
> till
> > now. Table lock is a overkill. So, I am trying to see whether I can
solve
> > the deadlock without using tablockx. The application can run for a day
to
> a
> > week. though a table lock would be held for a micro sec or so during the
> > update call, i am afraid it will create some other problems.

> > When I wrote the store proc intially I had only update and insert
> statement
> > with in the transaction. Run the update first and if the row count is 0
> the
> > stored proc inserts a record. That wasn't working and generating
duplicate
> > key errors. The documentation on transaction isolation level talks about
> > select statements. So, I thought I would start the stored proc with a
> select
> > statement to acquire a hold lock and do an update/insert depending on
> result
> > of select statement.



> > > Both threads will take a lock on the table to service the
> > > select and hold it due to the isolation level. Nether can
> > > then upgrade the lock to do the insert/update so one will
> > > deadlock.

> > > You only have problems with insert as the update will go
> > > through anyway (interested to know how you decide which
> > > update should go first and potentially be lost.

> > > Easy way out of this but taking exclusive lock.
> > > begin tran
> > >  update tableA (holdlock, tablockx)
> > > set col1 = col1 + <some value> where <conditionA>

> > > insert <record> into tableA
> > > commit tran

> > > >-----Original Message-----
> > > >Hello -

> > > >I am using transaction isolation level serializable in
> > > one of the stored
> > > >procedure and consistently getting deadlock if I have to
> > > more than three
> > > >application instances running pointing to the same
> > > database. The stored
> > > >procedure that I am executing is a simple one. It tries
> > > to check whether the
> > > >record exists in the table; if exists update the value in
> > > one column
> > > >otherwise inserts the record in the table. I can't the
> > > use the default
> > > >isolation level of read committed because it generate
> > > duplicate key errors.
> > > >(which I know why). Can anyone look the skeleton stored
> > > procedure below and
> > > >point out why it generates deadlocks? The stored
> > > procedure is operating on
> > > >only one table.

> > > >create procedure
> > > >....
> > > >begin
> > > >    set no count on
> > > >    set transaction isolation level serializable
> > > >    begin tran

> > > >        select col1 from tableA where <conditionA>

> > > >        beign
> > > >            insert <record> into tableA
> > > >        end
> > > >        else
> > > >        begin
> > > >            update tableA set col1 = col1 + <some value>
> > > where <conditionA>
> > > >        end

> > > >    commit tran
> > > >    set transation isolation level read committed  --
> > > revert back to default
> > > >setting
> > > >    set nocount off
> > > >end

> > > >thanks
> > > >uday

> > > >.

 
 
 

1. Set Transaction ISOLATION LEVEL Serializable

I have a

SET TRANSACTION ISOLATION LEVEL Serializable

statement at the beginning of my stored procedure.

Do I need a GO statement to get it to take effect before the

Begin Transaction

Statement?

In other words, is this OK?

SET TRANSACTION ISOLATION LEVEL Serializable
BEGIN Transaction

.....

Commit Transaction

OR do I need a GO statement BEFORE the BEGIN Transaction?

Thanks.

2. (no subject)

3. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE on 7.0

4. Export as Daily Backup

5. Transaction Isolation Level Serializable Problems

6. Possible to read foreign Btrieve data files?

7. Deadlocks and the Transaction Isolation Level

8. NEWBIE: copying a schema from one instance to another

9. Database Serializable Isolation Level

10. keyset cursors against linked servers requires repeatable_read or serializable isolation levels

11. Database Serializable Isolation Level

12. ISOLATION LEVEL SERIALIZABLE