Monday, May 03, 2004
SQL Sever, JDBC, deadlock
-----Original Message-----
From: Ludyk, Robert TMC [mailto:rludyk@tmctech.com]
Sent: Monday, May 03, 2004 8:18 AM
To: Willmott, Pat
Subject: RE: deadlock issue
The JDBC drivers for SQL Server are IBM branded data direct drivers. I ran a search for deadlocks on the Data Direct support site and found the solution there. The explanation is:
The deadlock will only happen when both SQL operations are hitting the same lock AND the transaction isolation level is set to SERIALIZABLE. If the isolation level is not SERIALIABLE but READ COMMITTED, it will still proceed without any problem even though one lock is shared by two SQL update operations.
The deadlock was caused by passing a Unicode string parameter that was to be compared with a non-unicode character column. Since the Unicode parameter can't be converted to a char to do the compare, SQL Server can't seek into the index on the column. Instead the index is scanned, with each row being converted to Unicode and compared to the Unicode parameter. The deadlock occurs because two separate queries will issue the index scan, and will each block on the other's updated row.
Resolution
There are two workarounds:
-----Original Message-----
From: Ludyk, Robert TMC [mailto:rludyk@tmctech.com]
Sent: Monday, May 03, 2004 8:18 AM
To: Willmott, Pat
Subject: RE: deadlock issue
The JDBC drivers for SQL Server are IBM branded data direct drivers. I ran a search for deadlocks on the Data Direct support site and found the solution there. The explanation is:
The deadlock will only happen when both SQL operations are hitting the same lock AND the transaction isolation level is set to SERIALIZABLE. If the isolation level is not SERIALIABLE but READ COMMITTED, it will still proceed without any problem even though one lock is shared by two SQL update operations.
The deadlock was caused by passing a Unicode string parameter that was to be compared with a non-unicode character column. Since the Unicode parameter can't be converted to a char to do the compare, SQL Server can't seek into the index on the column. Instead the index is scanned, with each row being converted to Unicode and compared to the Unicode parameter. The deadlock occurs because two separate queries will issue the index scan, and will each block on the other's updated row.
Resolution
There are two workarounds:
1. convert the character column to Unicode, or
2. add the parameter SendStringParametersAsUnicode=false to the server connection string.
Either will avoid the need to convert and the resulting scan.
Robert Ludyk
1. convert the character column to Unicode, or
2. add the parameter SendStringParametersAsUnicode=false to the server connection string.
Either will avoid the need to convert and the resulting scan.
Robert Ludyk
From: Ludyk, Robert TMC [mailto:rludyk@tmctech.com]
Sent: Monday, May 03, 2004 8:18 AM
To: Willmott, Pat
Subject: RE: deadlock issue
The JDBC drivers for SQL Server are IBM branded data direct drivers. I ran a search for deadlocks on the Data Direct support site and found the solution there. The explanation is:
The deadlock will only happen when both SQL operations are hitting the same lock AND the transaction isolation level is set to SERIALIZABLE. If the isolation level is not SERIALIABLE but READ COMMITTED, it will still proceed without any problem even though one lock is shared by two SQL update operations.
The deadlock was caused by passing a Unicode string parameter that was to be compared with a non-unicode character column. Since the Unicode parameter can't be converted to a char to do the compare, SQL Server can't seek into the index on the column. Instead the index is scanned, with each row being converted to Unicode and compared to the Unicode parameter. The deadlock occurs because two separate queries will issue the index scan, and will each block on the other's updated row.
Resolution
There are two workarounds:
-----Original Message-----
From: Ludyk, Robert TMC [mailto:rludyk@tmctech.com]
Sent: Monday, May 03, 2004 8:18 AM
To: Willmott, Pat
Subject: RE: deadlock issue
The JDBC drivers for SQL Server are IBM branded data direct drivers. I ran a search for deadlocks on the Data Direct support site and found the solution there. The explanation is:
The deadlock will only happen when both SQL operations are hitting the same lock AND the transaction isolation level is set to SERIALIZABLE. If the isolation level is not SERIALIABLE but READ COMMITTED, it will still proceed without any problem even though one lock is shared by two SQL update operations.
The deadlock was caused by passing a Unicode string parameter that was to be compared with a non-unicode character column. Since the Unicode parameter can't be converted to a char to do the compare, SQL Server can't seek into the index on the column. Instead the index is scanned, with each row being converted to Unicode and compared to the Unicode parameter. The deadlock occurs because two separate queries will issue the index scan, and will each block on the other's updated row.
Resolution
There are two workarounds:
1. convert the character column to Unicode, or
2. add the parameter SendStringParametersAsUnicode=false to the server connection string.
Either will avoid the need to convert and the resulting scan.
Robert Ludyk
1. convert the character column to Unicode, or
2. add the parameter SendStringParametersAsUnicode=false to the server connection string.
Either will avoid the need to convert and the resulting scan.
Robert Ludyk
