Deadlock within stored procedure - need help

We just went live today with a production SQL Server 2005 database
running with our custom Java application. We are utilizing the jTDS
open source driver. We migrated our existing application which was
using InterBase over to SQL Server. To minimize the impact to our
code, we created a stored procedure which would allow us to manage our
primary key IDs (mimicing the InterBase Generator construct). Now
that we have 150+ users in the system, we get the following error
periodically:

Caused by: java.sql.SQLException: Transaction (Process ID 115) was
deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDia gnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java :2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:222 4)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.jav a:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(Jtds Statement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery (JtdsPreparedStatement.java:
696)
at database.Generator.next(Generator.java:39)

Here is the script that creates our stored procedure:

USE [APPLAUSE]
GO
/****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
Date: 06/12/2007 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetGeneratorValue]
@genTableName varchar(50),
@Gen_Value int = 0 OUT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @Gen_Value = GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@genTableName
UPDATE GENERATOR SET GENVALUE = @Gen_Value+1 WHERE
GENTABLENAME=@genTableName
COMMIT;
SET @Gen_Value = @Gen_Value+1
SELECT @Gen_Value
END


This stored procedure is the ONLY place that the GENERATOR table is
being accessed. If anyone can provide any guidance on how to avoid
the deadlock errors, I would greatly appreciate it. The goal of this
stored procedure is to select the current value of the appropriate
record from the table and then increment it, ALL automically so that
there is no possibility of multiple processes getting the same IDs.

Re: Deadlock within stored procedure - need help

On Jun 12, 9:37 am, byahne wrote:
> We just went live today with a production SQL Server 2005 database
> running with our custom Java application. We are utilizing the jTDS
> open source driver. We migrated our existing application which was
> using InterBase over to SQL Server. To minimize the impact to our
> code, we created a stored procedure which would allow us to manage our
> primary key IDs (mimicing the InterBase Generator construct). Now
> that we have 150+ users in the system, we get the following error
> periodically:
>
> Caused by: java.sql.SQLException: Transaction (Process ID 115) was
> deadlocked on lock resources with another process and has been chosen
> as the deadlock victim. Rerun the transaction.
> at
> net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDia gnostic.java:
> 365)
> at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java :2781)
> at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:222 4)
> at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.jav a:633)
> at
> net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(Jtds Statement.java:
> 418)
> at
> net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery (JtdsPreparedStatement.java:
> 696)
> at database.Generator.next(Generator.java:39)
>
> Here is the script that creates our stored procedure:
>
> USE [APPLAUSE]
> GO
> /****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
> Date: 06/12/2007 10:27:14 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE PROCEDURE [dbo].[GetGeneratorValue]
> @genTableName varchar(50),
> @Gen_Value int = 0 OUT
> AS
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRAN
> SELECT @Gen_Value = GENVALUE FROM GENERATOR WHERE
> GENTABLENAME=@genTableName
> UPDATE GENERATOR SET GENVALUE = @Gen_Value+1 WHERE
> GENTABLENAME=@genTableName
> COMMIT;
> SET @Gen_Value = @Gen_Value+1
> SELECT @Gen_Value
> END
>
> This stored procedure is the ONLY place that the GENERATOR table is
> being accessed. If anyone can provide any guidance on how to avoid
> the deadlock errors, I would greatly appreciate it. The goal of this
> stored procedure is to select the current value of the appropriate
> record from the table and then increment it, ALL automically so that
> there is no possibility of multiple processes getting the same IDs.

1. Down your isolation level to REPEATABLE READ.
2. UPDATE first, then SELECT.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
UPDATE GENERATOR SET GENVALUE = GENVALUE + 1 WHERE
GENTABLENAME=@genTableName
SELECT GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@genTableName
COMMIT;


3. Consider allocating your numbers in batches rather than one at a
time.

Re: Deadlock within stored procedure - need help

Fantastic! That appears to have fixed the problem! Thank you for
your timely response.
-b

Re: Deadlock within stored procedure - need help

Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.

Any other words of wisdom on why this might be happening and how to
avoid it?

Re: Deadlock within stored procedure - need help

byahne (byahne@yahoo.com) writes:
> Actually, I spoke too soon. Even using the new stored procedure we
> are getting deadlock messages, but they are less periodic.
>
> Any other words of wisdom on why this might be happening and how to
> avoid it?

Did you also rewrite the procedure as Alex suggested? Or did you just
change the isolation level? In the latter case, you should add
"WITH (UPDLOCK)" to the SELECT query.

Else what happens is that two processes both get the read-lock on
the wrong, and then no one can procede with the UPDATE. Since only
one process at a time can hold an Update lock, one them will be held
up at this point - rather than both being held up later.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Deadlock within stored procedure - need help

In addition to Erland's suggestion, see
http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-s equence-number.aspx
for tweaks to the technique Alex suggested.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"byahne" wrote in message
news:1181745584.424801.302750@o11g2000prd.googlegroups.com.. .
> Actually, I spoke too soon. Even using the new stored procedure we
> are getting deadlock messages, but they are less periodic.
>
> Any other words of wisdom on why this might be happening and how to
> avoid it?
>