|
|
Posted by Alex Kuznetsov on 06/12/07 15:20
On Jun 12, 9:37 am, byahne <bya...@yahoo.com> 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(SQLDiagnostic.java:
> 365)
> at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
> at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
> at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:633)
> at
> net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.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.
Navigation:
[Reply to this message]
|