Deadlock in an application
- From: Vikram <Vikram@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 11 May 2007 06:39:00 -0700
I have tried the following.
1.
Findings : There are Selects, Inserts, Delete and Updates hapenning in the
same table 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 the 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.
.
- Follow-Ups:
- RE: Deadlock in an application
- From: Evan T. Basalik (MSFT)
- RE: Deadlock in an application
- Prev by Date: Re: selectMethod=cursor breaking query? wierd stuff.
- Next by Date: Re: JDBC Driver v1.2 April CTP, sqljdbc_auth.dll mandatory for windows clients ?
- Previous by thread: selectMethod=cursor breaking query? wierd stuff.
- Next by thread: RE: Deadlock in an application
- Index(es):
Relevant Pages
|
