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
> > > >.
by