Encountered this JDBC driver error when my multiple threaded client was inserting records to the MSSQL DB.
java.sql.SQLException: Transaction (Process ID 59) 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:368)
The quick way of solving it is to modify SQL server settings. From SMSS, advanced settings can be configured to help prevent unintentional issues such as a query that causes locking or blocking.
To access these settings in SSMS, choose Tools -> Options… -> Query Execution -> SQL Server -> Advanced dialog box.
1. Change SET TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED :
This will minimize the impact of your ad-hoc queries by allowing dirty reads. While this can be beneficial for many production environments, make sure to understand the implications of this setting before implementing.
2. Change SET DEADLOCK_PRIORITY to Low :
This will tell SQL Server to select your session as the victim in the event of a deadlock.
3. Change SET LOCK TIMEOUT to a smaller, defined value, such as 30000 milliseconds (30 seconds):
By default, SQL Server will wait forever for a lock to be released. By specifying a value, SQL Server will abort after the specified timeout period when a lock is encountered.
Though, this solution from server side solves the above issue, it is always not recommended as it could applies dirty read setting to all the DB instances and other transactionally important application may behave odd due to this.
java.sql.SQLException: Transaction (Process ID 59) 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:368)
The quick way of solving it is to modify SQL server settings. From SMSS, advanced settings can be configured to help prevent unintentional issues such as a query that causes locking or blocking.
To access these settings in SSMS, choose Tools -> Options… -> Query Execution -> SQL Server -> Advanced dialog box.
1. Change SET TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED :
This will minimize the impact of your ad-hoc queries by allowing dirty reads. While this can be beneficial for many production environments, make sure to understand the implications of this setting before implementing.
2. Change SET DEADLOCK_PRIORITY to Low :
This will tell SQL Server to select your session as the victim in the event of a deadlock.
3. Change SET LOCK TIMEOUT to a smaller, defined value, such as 30000 milliseconds (30 seconds):
By default, SQL Server will wait forever for a lock to be released. By specifying a value, SQL Server will abort after the specified timeout period when a lock is encountered.
Though, this solution from server side solves the above issue, it is always not recommended as it could applies dirty read setting to all the DB instances and other transactionally important application may behave odd due to this.
No comments:
Post a Comment