title: DB2 iSeries doesn't lock on select for update
body: <p>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.</p>
  
  <p>The behavior in the original application:</p>
  
  <ol>
  <li>Begin Transaction</li>
  <li>ExecuteReader () => Select col1 from table1 where col1 = 1 for update.</li>
  <li>The row is now locked. anyone else who tries to run Select for update should fail.</li>
  <li>Close the Reader opened in line 2.</li>
  <li>The row is now unlocked. - anyone else who tried to run select for update should succeed.</li>
  <li>Close transaction and live happily ever after.</li>
  </ol>
  
  <p>In my .NET code I have two problems:</p>
  
  <ol>
  <li><p>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</p></li>
  <li><p>Once that works - I need the lock to get unlocked when the reader is closed (step 4)</p></li>
  </ol>
  
  <p>Here's my code:</p>
  
  <pre><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");
      }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Any help would be greatly appreciated </p>