CODEWITHSUNDEEP
c#sql-servertransactionslocking
Ryan Johnson
Ryan Johnson

Reputation: 105

SQL Server lock preventing updates in transaction C#

I'm having an issue with a procedure that queries an item, and updates it in a loop. What happens is after the SELECT queries the items table, and updates the first item, whenever it comes around the 2nd time to query and update the next item, the SELECT query times out. 

using (IDbConnection dbConn = sql.createConnection(ProgOps.connectionString))
       {
       // Start Transaction
       dbConn.Open();
       IDbTransaction dbTrans = sql.createTransaction(dbConn);

        for (int i = 0; i < itemCount; i++)
            {
            // Get Item Dataset
            dbDataset =  sql.searchItemDetails(itemID[i]);     // SELECT * FROM Items WHERE itemID = @itemID

            // Update Item
            await sql.updateItem(itemID[i], dbConn, dbTrans);  // UPDATE Items SET price = @price WHERE itemID = @itemID
            }
       }

Looking at the processes in SSMS the items table wait type is in LCK_M_S which leads me to believe the table is locked preventing any more selects from occurring. I would assume that if each update was a row lock and not a page lock I would still be able to query and update other items correct? Anyways I'm unsure how to add these modifications to the transaction and individual queries using the code above, any suggestions are appreciated, Thanks.

Upvotes: 1

Views: 396

Answers (1)

Kenneth Garza
Kenneth Garza

Reputation: 1916

not entirely sure, but i can see how this code would lock. The transaction + a loop + a read + an update is pretty messy. Is there a reason you need to loop through this in a transaction? I would rewrite to this and see if it helps

using (IDbConnection dbConn = sql.createConnection(ProgOps.connectionString))
{
   // Start Transaction
   dbConn.Open();

    for (int i = 0; i < itemCount; i++)
    {
        // Get Item Dataset
        dbDataset =  sql.searchItemDetails(itemID[i]);     // SELECT * FROM Items WHERE itemID = @itemID

        IDbTransaction dbTrans = sql.createTransaction(dbConn);
        // Update Item
        await sql.updateItem(itemID[i], dbConn, dbTrans);  // UPDATE Items SET price = @price WHERE itemID = @itemID

        dbTrans.Commit(); // i think this is the command, pulling from memory
    }
}

If you need all of the updates to be a single transaction then I would update this code to do a bulk update instead of updating a single line at a time. Good luck

Upvotes: 1

Related Questions