Databases
StackOverflow2013
Postsdbo
PODeadlock in executing SQL statement and stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODeadlock in executing SQL statement and stored procedure
    primarykey
    data
    text
    <p>I am running a query like - <code>SELECT * FROM Table1 WHERE ID = id</code> from .Net window tool,</p> <p>And in case it contains any record, I am executing a stored procedure <code>[usp_DeleteTableData]</code> which is containing the below lines :</p> <pre><code>DELETE FROM Table1 WHERE ID = @id DELETE FROM Table2 WHERE ID = @id DELETE FROM Table3 WHERE ID = @id DELETE FROM Table4 WHERE ID = @id DELETE FROM Table5 WHERE ID = @id DELETE FROM Table6 WHERE ID = @id DELETE FROM Table7 WHERE ID = @id </code></pre> <p>I am facing dead lock issues like :</p> <pre><code>2013-11-13 16:06:12,787 [10] INFO MigrateInfo.Data Exception while running the sql query :EXEC [usp_DeleteTableData] 'AAAAAAABBBBBBBQWE' 2013-11-13 16:06:12,787 [10] FATAL MigrateInfo.Data System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 63) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean&amp; dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at MigrateInfo.Data.Command(String sql) in d:\PROJECTS\Data.cs:line 67 ClientConnectionId:c18a9bf8-19f3-4417-908f-33c48b26df14 </code></pre> <p>I want some help in keeping, both the query and stored procedure statements in one stored procedure with <code>LOCKING implemented</code>. So that it will check the existance of 'ID' and then proceed for the deletion, with out deadlock. </p> <p>Information - I have not used any locking in this</p> <p>Please let me know, if I need to provide any more information.</p> <p><strong>EDIT</strong></p> <p><strong>The SELECT - DELETE job is multi threaded from .Net window tool for different IDs.</strong></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJames
    1. USJames
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COIs ID a PK? Is it clustered? Is the app holding the SELECT open in some way? Does this only happen when multiple users try to perform similar or the same delete?
      singulars
      1. PODeadlock in executing SQL statement and stored procedure
      1. USAaron Bertrand
    2. CONo..Its a unique ID in the table.
      singulars
      1. PODeadlock in executing SQL statement and stored procedure
      1. USJames
    3. COIts a window tool running on system..and is the only thing that is running on that database.
      singulars
      1. PODeadlock in executing SQL statement and stored procedure
      1. USJames
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload