By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,563 Members | 1,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,563 IT Pros & Developers. It's quick & easy.

Deadlock within stored procedure - need help

P: n/a
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.

Jun 12 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Jun 12, 9:37 am, byahne <bya...@yahoo.comwrote:
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.

Jun 12 '07 #2

P: n/a
Fantastic! That appears to have fixed the problem! Thank you for
your timely response.
-b
Jun 12 '07 #3

P: n/a
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?

Jun 13 '07 #4

P: n/a
byahne (by****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 13 '07 #5

P: n/a
In addition to Erland's suggestion, see
http://blogs.msdn.com/sqlcat/archive...ce-number.aspx
for tweaks to the technique Alex suggested.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"byahne" <by****@yahoo.comwrote in message
news:11**********************@o11g2000prd.googlegr oups.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?
Jun 16 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.