Read committed, no locks. Screwed up

Question

I have a weird problem from production systems. I have readCommitted transaction defined with read operation with no_lock and write operation with table lock in the same scope.

However, multiple processes are able to override the lock and able to perform write operation with great impact. Please help!. Thanks.

using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
{
    dataAccess.ExecNonQuery("GetNextOrder", parametersList); //read orders from table 1, there is NO_lock in the SP

    orderIDParameter = (IParameter)parametersList[0];
    int orderIDParameterValue = (int) orderIDParameter.Value;

    if (orderIDParameterValue != NullOrderID)
    {
       parametersList.Clear();
       IParameter orderIDLockParameter = SQLClientFactory.GetParameter("@OrderID", orderIDParameterValue, Direction.In);
       IParameter lockNameParameter = SQLClientFactory.GetParameter("@LockName", lockName, Direction.In);
       parametersList.Add(orderIDLockParameter);
       parametersList.Add(lockNameParameter);

       dataAccess.ExecNonQuery("Insert_SP", parametersList);  //Insert into table 2

       orderID = orderIDParameterValue;
    }

    transaction.Complete();
}

Stored procedure: GetNextOrder

SELECT top 1 @OrderID=ht.OrderID        
     FROM dbo.table1 ht WITH(nolock)       
     left join dbo.table2 htl WITH(nolock) on ht.orderid = htl.orderid        
     WHERE ht.Status in (0,1)

Stored procedure: Insert_SP

CREATE procedure [dbo].[Insert_SP] (
@OrderID INT,
@LockName VARCHAR(50)
   )
AS
BEGIN
SET NOCOUNT ON

 insert into table2 (OrderID, LockName) VALUES (@OrderID, @LockName) 

SET NOCOUNT OFF
END

So how does the other SP look? Under which circumstances do you have a problem? — erikkallen, Sep 13 '12 at 11:53
I have updated the SP. One possibility is that multiple processes calling the same code. Thanks for the reply. — Adarsh, Sep 13 '12 at 12:00
How have you determined that "multiple processes are able to override the lock and able to perform write operation with great impact"? And btw, you are not using a table lock in your insert SB but a row lock. — erikkallen, Sep 13 '12 at 12:03
Still in the process of finding whether multiple processes is the problem. Primary key constraint is defined for table 2. I believe in SQL lock defined is equivalent to rowlock and table lock. — Adarsh, Sep 13 '12 at 12:10
On primary key constraint, we have thread.sleep defined. if (e.InnerException.ToString().Contains("PK_Table2_OrderID")) { _pcsLogger.AddLogEntry(Logger.Severity.Warning, "OperationsProcessor->Attempting to re-try next transaction."); Thread.Sleep(_pollingInterval); } — Adarsh, Sep 13 '12 at 12:32

score 0 · Answer 1 · answered Sep 13 '12 at 12:37

0

How is your dataAccess class defined?

I suspect it's not enlisting in the new transaction - use

connection.EnlistTransaction(Transaction.Current)

in that class.

answered Sep 13 '12 at 12:37

podiluska

50,950
7
98
104

Each process has only one thread. How does enlisting the transaction help? – Adarsh Sep 13 '12 at 12:49
@Adarsh Well, without that, the transaction scope won't apply to your code. – podiluska Sep 13 '12 at 15:05

Read committed, no locks. Screwed up

1 Answers1