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.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(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.On Jun 12, 9:37 am, byahne <bya...@.yahoo.comwrote:

Quote:

Originally Posted by

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.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(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.|||Fantastic! That appears to have fixed the problem! Thank you for
your timely response.
-b|||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?|||byahne (byahne@.yahoo.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to Erland's suggestion, see
http://blogs.msdn.com/sqlcat/archiv...nce-number.aspx
for tweaks to the technique Alex suggested.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"byahne" <byahne@.yahoo.comwrote in message
news:1181745584.424801.302750@.o11g2000prd.googlegr oups.com...

Quote:

Originally Posted by

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?
>