Deadlock issue (JAVA + SQL server)

I have a java web based application, which was build and designed keeping
oracle in mind. The application also used a 3rd party API "cocobase" which
used to do most of the data interraction. But there were many issue after the
application is ported to work with SQL database and the most prominant issue
is a Deadlock issue. We need to resolve the deadlock issue first. I found out
the deadlock is happening one particular table say 'tableX'
I have tried the following.
1.
Findings : There are Selects, Inserts, Delete and Updates hapenning in the
same table (tableX) for numerous times. These DML statements were executed
using cocobase calls. The Insert, Delete and Updates were targeted first as
deadlock were happening mostly after an Insert or Update. Insert/Update was
taking some time to execute.
Action taken : The Insert/Delete/Updates were changed from cocobase calls to
direct jdbc calls.
Benefit : The time taken by cocobase to prepare the statement was saved.
2.
Findings : After the change, the Insert/Updates were being executed as
normal sql statements using direct TSQL e.g insert into table1 values ('abc',
123). Each time the query is executed the sql engine would compile the
statement.
Action taken : The Insert/Updates were changed to execute using prepared
statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
varchar, @.p2 int', 'abc', 123
Benefit : The statements used to execute faster. If the query is fired once,
then the sql engine will not compile the statement the next time it is fired
(even by other transactions) with different parameter values.
3.
Findings : After the changes, the deadlock still persist, but this time the
deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
execution. It was found the the select statement were opening cursors and the
cursors were getting closed in the end while closing the connection object.
The cursor or statements need to be closed right after use.
Action taken : The "Select" statements were changed from cocobase to direct
jdbc calls. Most of the cursors or statements pertaining to the product table
were closed after use. There were 2 or 3 places where the changes couldn't be
applied cause of the complexity in the code. The recordset object itself were
being dynamically created and used in a baseclass.
Benefit : The locking period of a resource is reduced, if the
cursor/statement is closed after use.
4.
Findings : Every select query was opening a cursor which used to lock the
resource in the database. Using cursor was not necessary as in the
application when a query is fired or when a data is fetched, it is ideally
pertaining to one batch, which is not very huge. If we could avoid using
cursor, the locking would reduce and would help us resolve the deadlock issue.
Action taken : The "SelectMethod" in the connectionstring or URL was changed
from "Cursor" to "Direct". But due to the changes, the application chrashed
and the change didnt work. Hence it was reverted back.
Benefit : None, as the changes didnt work for the application.
5.
Findings : The other cocobase calls to their tables like "CB_TABLES",
"CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
There was a index on the OBJECTNAME field, but was a nonclustered index.
Action taken : The existing nonclustered index was changed to a clustered
index.
Benefit : The query executed faster as initially the sql engine was doing a
"table scan" and now it was doing a "cluster index seek".
Is there anything else anyone suggest to handle and resolve the deadlock?
Any advise would be appreciated.
Cheers
Hi
"Vikram" wrote:

> I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after the
> application is ported to work with SQL database and the most prominant issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first as
> deadlock were happening mostly after an Insert or Update. Insert/Update was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired once,
> then the sql engine will not compile the statement the next time it is fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and the
> cursors were getting closed in the end while closing the connection object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direct
> jdbc calls. Most of the cursors or statements pertaining to the product table
> were closed after use. There were 2 or 3 places where the changes couldn't be
> applied cause of the complexity in the code. The recordset object itself were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was changed
> from "Cursor" to "Direct". But due to the changes, the application chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
You don't give the version of SQL Server you are using? There are
enhancements in SQL Server 2005 that make these easier to track down and
diagnose.
If you process is actually being blocked and not deadlocked then check out:
http://support.microsoft.com/default.aspx/kb/271509/EN-US/
If you have deadlocks it usually require two or more processes that trying
to access the same resources but in different orders. See
http://support.microsoft.com/kb/832524/en-us
John
|||On May 16, 9:58 am, Vikram <Vik...@.discussions.microsoft.com> wrote:
> I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after the
> application is ported to work with SQL database and the most prominant issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first as
> deadlock were happening mostly after an Insert or Update. Insert/Update was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired once,
> then the sql engine will not compile the statement the next time it is fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and the
> cursors were getting closed in the end while closing the connection object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direct
> jdbc calls. Most of the cursors or statements pertaining to the product table
> were closed after use. There were 2 or 3 places where the changes couldn't be
> applied cause of the complexity in the code. The recordset object itself were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was changed
> from "Cursor" to "Direct". But due to the changes, the application chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
1. Are you using trasaction . If yes , try to make duration of
transaction execution short by removing unwanted statements from the
transaction
2. Eventhough you have created indexes sql server may go for table
scan . Check the execution plan and provide appropriate index hints
on tables like
from table with (index(indexname))
3. Use the tables in the same order in different transactions
4. What is the isolation level used . If it is SERIALIZABLE, check
whether READ COMMITED is OK for your logic
5. If you use components , default isolation level may be SERIALIZABLE
try to change it to READ COMMITED
|||> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
In addition to John's suggestions, take a look at execution plans of other
queries to ensure data are accessed using seeks rather than scans. Scans
are notorious for contributing to deadlocking and blocking. Also, you might
need to add locking hints in certain situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vikram" <Vikram@.discussions.microsoft.com> wrote in message
news:BC820B12-579C-49A8-918F-3CD10973C270@.microsoft.com...
>I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after
> the
> application is ported to work with SQL database and the most prominant
> issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found
> out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first
> as
> deadlock were happening mostly after an Insert or Update. Insert/Update
> was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls
> to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values
> ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)',
> '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired
> once,
> then the sql engine will not compile the statement the next time it is
> fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time
> the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and
> the
> cursors were getting closed in the end while closing the connection
> object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to
> direct
> jdbc calls. Most of the cursors or statements pertaining to the product
> table
> were closed after use. There were 2 or 3 places where the changes couldn't
> be
> applied cause of the complexity in the code. The recordset object itself
> were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock
> issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was
> changed
> from "Cursor" to "Direct". But due to the changes, the application
> chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
>
sql