CODEWITHSUNDEEP
db2-400
Noam
Noam

Reputation: 5286

DB2 iSeries doesn't lock on select for update

I'm migrating a legacy application using DB2 iSeries on AS400 that has a specific behavior that I have to reproduce using .NET and DB2.Data.DB2.iSeries client for .NET. What I'm describing works for me with DB2 non AS400 but in AS400 DB2 it worlks for the legacy application i'm replacing - but not with my application.

The behavior in the original application:

  1. Begin Transaction
  2. ExecuteReader () => Select col1 from table1 where col1 = 1 for update.
  3. The row is now locked. anyone else who tries to run Select for update should fail.
  4. Close the Reader opened in line 2.
  5. The row is now unlocked. - anyone else who tried to run select for update should succeed.
  6. Close transaction and live happily ever after.

In my .NET code I have two problems:

  1. Step 2 - only checks if the row is already locked - but doesn't actually lock it. so another user can and does run select for update - WRONG BEHAVIOUR

  2. Once that works - I need the lock to get unlocked when the reader is closed (step 4)

Here's my code:

var cb = new IBM.Data.DB2.iSeries.iDB2ConnectionStringBuilder();
cb.DataSource = "10.0.0.1";
cb.UserID = "User";
cb.Password = "Password";
using (var con = new IBM.Data.DB2.iSeries.iDB2Connection(cb.ToString()))
{

    con.Open();
    var t = con.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
    using (var c = con.CreateCommand())
    {
        c.Transaction = t;
        c.CommandText = "select col1 from table1 where col1=1 FOR UPDATE";
        using (var r = c.ExecuteReader())
        {
            while (r.Read()) {
                MessageBox.Show(con.JobName +  "The Row Should Be Locked");
            }
        }
        MessageBox.Show(con.JobName +  "The Row Should Be unlocked");
    }
}

When you run this code twice - you'll see both processes reach the "This row should be locked" which is the problem I'm describing.

The desired result would be that the first process will reach the "This row should be locked" and that the second process will fail with resource busy error.

Then when the first process reaches the second message box - "the row should be unlocked" the second process( after running again ) will reach the "This row should be locked" message.

Any help would be greatly appreciated

Upvotes: 0

Views: 1888

Answers (3)

Noam
Noam

Reputation: 5286

After much investigations we created a work around in the form of a stored procedure that performs the lock for us.

The stored procedure looks like this:

CREATE PROCEDURE lib.Select_For_Update  (IN SQL CHARACTER (5000) ) 
    MODIFIES SQL DATA CONCURRENT ACCESS RESOLUTION WAIT FOR OUTCOME 
    DYNAMIC RESULT SETS 1 OLD SAVEPOINT LEVEL COMMIT ON RETURN 
    NO DISALLOW DEBUG MODE SET OPTION COMMIT = *CHG BEGIN
DECLARE X CURSOR WITH RETURN TO CLIENT FOR SS ;
PREPARE SS FROM SQL ;
OPEN X ;
END

Then we call it using:

var cb = new IBM.Data.DB2.iSeries.iDB2ConnectionStringBuilder();
cb.DataSource = "10.0.0.1";
cb.UserID = "User";
cb.Password = "Password";
using (var con = new IBM.Data.DB2.iSeries.iDB2Connection(cb.ToString()))
{

    con.Open();
    var t = con.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
    using (var c = con.CreateCommand())
    {
        c.Transaction = t;
        c.CommandType = CommandType.StoredProcedure;
        c.AddParameter("sql","select col1 from table1 where col1=1 FOR UPDATE");
        c.CommandText = "lib.Select_For_Update"
        using (var r = c.ExecuteReader())
        {
            while (r.Read()) {
                MessageBox.Show(con.JobName +  "The Row Should Be Locked");
            }
        }
        MessageBox.Show(con.JobName +  "The Row Should Be unlocked");
    }
}

We don't like it - but it works.

Upvotes: 0

danny117
danny117

Reputation: 5651

Isolation Level allows this behavior. Reading rows that are locked.

ReadUncommitted A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.

Upvotes: 0

jmarkmurphy
jmarkmurphy

Reputation: 11473

The documentation says:

When the UPDATE clause is used, FETCH operations referencing the cursor acquire an exclusive row lock.

This implies a cursor is being used, and the lock occurs when the fetch statement is executed. I don't see a cursor, or a fetch in your code.

Now, whether .NET handles this as a cursor, I don't know, but the DB2 UDB documentation does not have this notation.

Upvotes: 1

Related Questions