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
> --
>
Showing posts with label build. Show all posts
Showing posts with label build. Show all posts
Wednesday, March 21, 2012
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
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
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 th
e
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 ou
t
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 th
e
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 tabl
e
were closed after use. There were 2 or 3 places where the changes couldn't b
e
applied cause of the complexity in the code. The recordset object itself wer
e
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 issu
e.
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 iss
ue
> 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 a
s
> deadlock were happening mostly after an Insert or Update. Insert/Update wa
s
> 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 ('ab
c',
> 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 (?, ?)', '@.p
1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired onc
e,
> then the sql engine will not compile the statement the next time it is fir
ed
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time th
e
> 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 direc
t
> jdbc calls. Most of the cursors or statements pertaining to the product ta
ble
> 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 w
ere
> 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 is
sue.
> Action taken : The "SelectMethod" in the connectionstring or URL was chang
ed
> from "Cursor" to "Direct". But due to the changes, the application chrash
ed
> 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/defaul...b/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 iss
ue
> 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 a
s
> deadlock were happening mostly after an Insert or Update. Insert/Update wa
s
> 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 ('ab
c',
> 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 (?, ?)', '@.p
1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired onc
e,
> then the sql engine will not compile the statement the next time it is fir
ed
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time th
e
> 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 direc
t
> jdbc calls. Most of the cursors or statements pertaining to the product ta
ble
> 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 w
ere
> 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 is
sue.
> Action taken : The "SelectMethod" in the connectionstring or URL was chang
ed
> from "Cursor" to "Direct". But due to the changes, the application chrash
ed
> 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
> --
>
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 th
e
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 ou
t
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 th
e
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 tabl
e
were closed after use. There were 2 or 3 places where the changes couldn't b
e
applied cause of the complexity in the code. The recordset object itself wer
e
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 issu
e.
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 iss
ue
> 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 a
s
> deadlock were happening mostly after an Insert or Update. Insert/Update wa
s
> 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 ('ab
c',
> 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 (?, ?)', '@.p
1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired onc
e,
> then the sql engine will not compile the statement the next time it is fir
ed
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time th
e
> 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 direc
t
> jdbc calls. Most of the cursors or statements pertaining to the product ta
ble
> 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 w
ere
> 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 is
sue.
> Action taken : The "SelectMethod" in the connectionstring or URL was chang
ed
> from "Cursor" to "Direct". But due to the changes, the application chrash
ed
> 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/defaul...b/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 iss
ue
> 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 a
s
> deadlock were happening mostly after an Insert or Update. Insert/Update wa
s
> 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 ('ab
c',
> 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 (?, ?)', '@.p
1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired onc
e,
> then the sql engine will not compile the statement the next time it is fir
ed
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time th
e
> 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 direc
t
> jdbc calls. Most of the cursors or statements pertaining to the product ta
ble
> 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 w
ere
> 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 is
sue.
> Action taken : The "SelectMethod" in the connectionstring or URL was chang
ed
> from "Cursor" to "Direct". But due to the changes, the application chrash
ed
> 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
> --
>
Sunday, March 11, 2012
Deadlock between Distribution Agent and Distribution Agent Cleanup
This is occurring regularly on SQL Server 2000 build 878.
The problem is a deadlock in the Distribution database. The Distribution
Agent spid is executing the SELECT statement below:
select @.max_xact_seqno = max(xact_seqno) from MSrepl_commands (READPAST)
where
publisher_database_id = @.publisher_database_id and
command_id = 1 and
type <> -2147483611
which is found in sp_MSget_repl_commands. It holds an Intent Shared page
lock on a data page in the MSrepl_commands table.
The Distribution Agent Cleanup spid is found to be running the command below:
DELETE MSrepl_commands WITH (PAGLOCK) where
publisher_database_id = @.publisher_database_id and
xact_seqno <= @.max_xact_seqno
located in the stored procedure sp_MSdelete_publisherdb_trans. This spid
holds an exclusive page lock on another data page in MSrepl_commands.
Both spids then attempt to obtain the same lock type on the page which is
locked by the other.
The Distribution Agent runs continuously and the Cleanup job is scheduled
for every 10 minutes. The Publication, Distribution and Subscription
databases are all on the same instance (3rd party vendor solution, not mine!)
in an active/active Win2003 cluster configuration. The articles are all
stored procedure executions.
Has anybody else seen this deadlock? Is it just a timing issue? Why is the
PAGLOCK hint used in sp_MSdelete_publisherdb_trans as above?
(I can't find any articles which correlate exactly to this problem)
Kind Regards
Andrew Pike
SQL Server DBA
Accenture UK
Do you have anonymous subscribers or named. With named subscribers the
distribution clean up agent cleans up more aggressively and you may see
problems like this when a subscriber has been offline for some time.
First off issue a select * from distribution.dbo.MSdistribution_status to
see how many undelivered vs delivered commands there are. If there are a
high number of delivered commands, I would stop the SQL Server Agent and run
the distribution clean up agent manually.
I can't comment on why the decision was made to implement the two types of
locks, but in general MS has done a lot of research to deliver optimal
performance. For example the 27 in sp_MSadd_repl_commands27 comes from
tests that they did to find the optimal number of commands to send to the
distribution database in a batch from the log reader agent. And yes, they
tested a range of commands to find which offered best performance.
It looks like the readpast is to prevent locking, and the page lock is to
prevent a table lock.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:CFB322D7-1D17-4064-AB36-6338A49C90A4@.microsoft.com...
> This is occurring regularly on SQL Server 2000 build 878.
> The problem is a deadlock in the Distribution database. The Distribution
> Agent spid is executing the SELECT statement below:
> select @.max_xact_seqno = max(xact_seqno) from MSrepl_commands (READPAST)
> where
> publisher_database_id = @.publisher_database_id and
> command_id = 1 and
> type <> -2147483611
> which is found in sp_MSget_repl_commands. It holds an Intent Shared page
> lock on a data page in the MSrepl_commands table.
> The Distribution Agent Cleanup spid is found to be running the command
> below:
> DELETE MSrepl_commands WITH (PAGLOCK) where
> publisher_database_id = @.publisher_database_id and
> xact_seqno <= @.max_xact_seqno
> located in the stored procedure sp_MSdelete_publisherdb_trans. This spid
> holds an exclusive page lock on another data page in MSrepl_commands.
> Both spids then attempt to obtain the same lock type on the page which is
> locked by the other.
> The Distribution Agent runs continuously and the Cleanup job is scheduled
> for every 10 minutes. The Publication, Distribution and Subscription
> databases are all on the same instance (3rd party vendor solution, not
> mine!)
> in an active/active Win2003 cluster configuration. The articles are all
> stored procedure executions.
> Has anybody else seen this deadlock? Is it just a timing issue? Why is
> the
> PAGLOCK hint used in sp_MSdelete_publisherdb_trans as above?
> (I can't find any articles which correlate exactly to this problem)
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> Accenture UK
>
The problem is a deadlock in the Distribution database. The Distribution
Agent spid is executing the SELECT statement below:
select @.max_xact_seqno = max(xact_seqno) from MSrepl_commands (READPAST)
where
publisher_database_id = @.publisher_database_id and
command_id = 1 and
type <> -2147483611
which is found in sp_MSget_repl_commands. It holds an Intent Shared page
lock on a data page in the MSrepl_commands table.
The Distribution Agent Cleanup spid is found to be running the command below:
DELETE MSrepl_commands WITH (PAGLOCK) where
publisher_database_id = @.publisher_database_id and
xact_seqno <= @.max_xact_seqno
located in the stored procedure sp_MSdelete_publisherdb_trans. This spid
holds an exclusive page lock on another data page in MSrepl_commands.
Both spids then attempt to obtain the same lock type on the page which is
locked by the other.
The Distribution Agent runs continuously and the Cleanup job is scheduled
for every 10 minutes. The Publication, Distribution and Subscription
databases are all on the same instance (3rd party vendor solution, not mine!)
in an active/active Win2003 cluster configuration. The articles are all
stored procedure executions.
Has anybody else seen this deadlock? Is it just a timing issue? Why is the
PAGLOCK hint used in sp_MSdelete_publisherdb_trans as above?
(I can't find any articles which correlate exactly to this problem)
Kind Regards
Andrew Pike
SQL Server DBA
Accenture UK
Do you have anonymous subscribers or named. With named subscribers the
distribution clean up agent cleans up more aggressively and you may see
problems like this when a subscriber has been offline for some time.
First off issue a select * from distribution.dbo.MSdistribution_status to
see how many undelivered vs delivered commands there are. If there are a
high number of delivered commands, I would stop the SQL Server Agent and run
the distribution clean up agent manually.
I can't comment on why the decision was made to implement the two types of
locks, but in general MS has done a lot of research to deliver optimal
performance. For example the 27 in sp_MSadd_repl_commands27 comes from
tests that they did to find the optimal number of commands to send to the
distribution database in a batch from the log reader agent. And yes, they
tested a range of commands to find which offered best performance.
It looks like the readpast is to prevent locking, and the page lock is to
prevent a table lock.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:CFB322D7-1D17-4064-AB36-6338A49C90A4@.microsoft.com...
> This is occurring regularly on SQL Server 2000 build 878.
> The problem is a deadlock in the Distribution database. The Distribution
> Agent spid is executing the SELECT statement below:
> select @.max_xact_seqno = max(xact_seqno) from MSrepl_commands (READPAST)
> where
> publisher_database_id = @.publisher_database_id and
> command_id = 1 and
> type <> -2147483611
> which is found in sp_MSget_repl_commands. It holds an Intent Shared page
> lock on a data page in the MSrepl_commands table.
> The Distribution Agent Cleanup spid is found to be running the command
> below:
> DELETE MSrepl_commands WITH (PAGLOCK) where
> publisher_database_id = @.publisher_database_id and
> xact_seqno <= @.max_xact_seqno
> located in the stored procedure sp_MSdelete_publisherdb_trans. This spid
> holds an exclusive page lock on another data page in MSrepl_commands.
> Both spids then attempt to obtain the same lock type on the page which is
> locked by the other.
> The Distribution Agent runs continuously and the Cleanup job is scheduled
> for every 10 minutes. The Publication, Distribution and Subscription
> databases are all on the same instance (3rd party vendor solution, not
> mine!)
> in an active/active Win2003 cluster configuration. The articles are all
> stored procedure executions.
> Has anybody else seen this deadlock? Is it just a timing issue? Why is
> the
> PAGLOCK hint used in sp_MSdelete_publisherdb_trans as above?
> (I can't find any articles which correlate exactly to this problem)
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> Accenture UK
>
Thursday, March 8, 2012
dead lock problem
Version: SQL Server 2000 8.00.818
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have been asked to look into a problem in one of the database
at our client site. I have very little idea of the application.
It seems they are facing intermittent deadlock problem. This is the
query of the session which is *always* rolled back.
SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
...
FROM air_itin_price, air_itin_fare_calc
WHERE air_itin_price.air_itin_id = ?
AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_id
order by air_itin_fare_calc.air_itin_price_id
The index on the two tables
ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
(
[air_itin_id],
[psgr_type]
) WITH FILLFACTOR = 50 ON [PRIMARY]
CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
[dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMARY]
CREATE INDEX [air_itin_price_airitinpriceid] ON
[dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
This is a read only query only, even though the isolation level is same for
all sessions (SERIALIZABLE).
Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer wi
ll
use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behavio
r
same with CLUSTERED INDEX also. I also notice that the primary key on the ta
ble
air_itin_price is a composite index on air_itin_id + psgr_type. But the quer
y
is only for air_itin_id. Does that make a difference?
Any pointers will be appreciated."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_i
d
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same fo
r
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behav
ior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the qu
ery
> is only for air_itin_id. Does that make a difference?
> Any pointers will be appreciated.
some more info from trace:=
Deadlock encountered ... Printing deadlock information
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Wait-for graph
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:1
2004-03-19 14:12:34.65 spid4 PAG: 6:1:3120 CleanCnt:1 M
ode: S Flags: 0x2
2004-03-19 14:12:34.65 spid4 Grant List 0::
2004-03-19 14:12:34.65 spid4 Owner:0x42bcba80 Mode: S Flg:0x0
Ref:1 Life:00000000
SPID:178 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 178 ECID: 0 Statement Type: EXECUT
E Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: RPC Event: sp_cursorfetch;1
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: IX SP
ID:76 ECID:0
Ec
0x713CF510) Value:0x42bd18e0 Cost0/3F0)
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:2
2004-03-19 14:12:34.65 spid4 PAG: 6:1:10267 CleanCnt:1 M
ode: IX Flags: 0x0
2004-03-19 14:12:34.65 spid4 Grant List 2::
2004-03-19 14:12:34.65 spid4 Owner:0x42bd3080 Mode: IX Flg:0x0
Ref:0 Life:02000000
SPID:76 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 76 ECID: 0 Statement Type: INSERT
Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: Language Event: INSERT INTO a
ir_itin_price
(air_itin_id,psgr_type,quantity,pub_fare
,base_fare,q_charge,other_charges,tt
l_markup,ttl_tax,securit
y_fee,fare_tax_rate, us1_tax) VALUES
(398529,0,1,237.24,183.10,0.00,0.00,0.00,44.14,10.00,0.0000,13.74)
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPI
D:178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
2004-03-19 14:12:34.65 spid4 Victim Resource Owner:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:
178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
Looks like it is a conversion deadlock.|||both sessions are using READ COMMITTED.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id =
air_itin_fare_calc.air_itin_price_id
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same
for
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the
behavior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the
query
> is only for air_itin_id. Does that make a difference?
>
Perhaps. If the query used the key, then it's locks would be more narrow.
It looks like the row being inserted by one client might belong in the
resultset of for the other client. If the query specified the full key, it
might be clear to SQL that that is not the case.
Also the query is using
sp_cursorfetch;1
What kind of cursor is the client using?
David
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have been asked to look into a problem in one of the database
at our client site. I have very little idea of the application.
It seems they are facing intermittent deadlock problem. This is the
query of the session which is *always* rolled back.
SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
...
FROM air_itin_price, air_itin_fare_calc
WHERE air_itin_price.air_itin_id = ?
AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_id
order by air_itin_fare_calc.air_itin_price_id
The index on the two tables
ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
(
[air_itin_id],
[psgr_type]
) WITH FILLFACTOR = 50 ON [PRIMARY]
CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
[dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMARY]
CREATE INDEX [air_itin_price_airitinpriceid] ON
[dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
This is a read only query only, even though the isolation level is same for
all sessions (SERIALIZABLE).
Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer wi
ll
use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behavio
r
same with CLUSTERED INDEX also. I also notice that the primary key on the ta
ble
air_itin_price is a composite index on air_itin_id + psgr_type. But the quer
y
is only for air_itin_id. Does that make a difference?
Any pointers will be appreciated."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_i
d
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same fo
r
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behav
ior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the qu
ery
> is only for air_itin_id. Does that make a difference?
> Any pointers will be appreciated.
some more info from trace:=
Deadlock encountered ... Printing deadlock information
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Wait-for graph
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:1
2004-03-19 14:12:34.65 spid4 PAG: 6:1:3120 CleanCnt:1 M
ode: S Flags: 0x2
2004-03-19 14:12:34.65 spid4 Grant List 0::
2004-03-19 14:12:34.65 spid4 Owner:0x42bcba80 Mode: S Flg:0x0
Ref:1 Life:00000000
SPID:178 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 178 ECID: 0 Statement Type: EXECUT
E Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: RPC Event: sp_cursorfetch;1
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: IX SP
ID:76 ECID:0
Ec
0x713CF510) Value:0x42bd18e0 Cost0/3F0)2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:2
2004-03-19 14:12:34.65 spid4 PAG: 6:1:10267 CleanCnt:1 M
ode: IX Flags: 0x0
2004-03-19 14:12:34.65 spid4 Grant List 2::
2004-03-19 14:12:34.65 spid4 Owner:0x42bd3080 Mode: IX Flg:0x0
Ref:0 Life:02000000
SPID:76 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 76 ECID: 0 Statement Type: INSERT
Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: Language Event: INSERT INTO a
ir_itin_price
(air_itin_id,psgr_type,quantity,pub_fare
,base_fare,q_charge,other_charges,tt
l_markup,ttl_tax,securit
y_fee,fare_tax_rate, us1_tax) VALUES
(398529,0,1,237.24,183.10,0.00,0.00,0.00,44.14,10.00,0.0000,13.74)
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPI
D:178 ECID:0
Ec
0x716F1548) Value:0x42bca6c0 Cost0/0)2004-03-19 14:12:34.65 spid4 Victim Resource Owner:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:
178 ECID:0
Ec
0x716F1548) Value:0x42bca6c0 Cost0/0)Looks like it is a conversion deadlock.|||both sessions are using READ COMMITTED.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id =
air_itin_fare_calc.air_itin_price_id
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same
for
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the
behavior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the
query
> is only for air_itin_id. Does that make a difference?
>
Perhaps. If the query used the key, then it's locks would be more narrow.
It looks like the row being inserted by one client might belong in the
resultset of for the other client. If the query specified the full key, it
might be clear to SQL that that is not the case.
Also the query is using
sp_cursorfetch;1
What kind of cursor is the client using?
David
Subscribe to:
Posts (Atom)