Sharing Recordsets

For recordsets and SQL statements, Jet uses smart defaults to determine which page and record locking settings to use. With ADO code that relies on cursors, Jet defaults to record-level locking. With SQL statements that can easily affect thousands of rows, Jet switches to page-level locking as the default. This, in turn, lets your application benefit from lock promotion (if lock promotion is enabled).

When you use row-level locking with recordsets, you achieve concurrency and performance enhancements only if you explicitly wrap recordset maintenance tasks in transactions. You use the BeginTrans, CommitTrans, and Rollback methods (discussed later in this chapter) to wrap each record operation. Without this precaution, locks accumulate until they reach the value of the FlushTransactionTimeout registry setting.

When you use record-level locking, you should carefully consider the data types for fields and record lengths to control database file size growth. Record-level locking writes rows rather than pages; rows that extend across a page boundary must be moved to a new page. Using fixed-length data types, you can determine the precise length of records; you cannot do this with the variable-length character fields for Access tables. (However, Jet SQL's data definition language includes a CHAR data type that lets you determine the length of string data type fields. This, in turn, can help to control record size bloat caused by records that do not fit entirely within one page.)

Row-Level Locking

The following procedure illustrates row locking in a multi-user application. It simulates a two-user application by running two connections against the same data file. The application also requires a second file named TestRowLock.mdb. Run the procedure in step mode to see how the row locking succeeds and fails under different circumstances.

Sub RowLockingPessimistic() 'Use TestRowLocking.mdb to run this demo of  'row locking; DO NOT HAVE TESTROWLOCK.MDB OPEN  'WHEN RUNNING THE DEMO. Dim cnn1 As New ADODB.Connection Dim cnn2 As New ADODB.Connection Dim rs As New ADODB.Recordset Dim rs2 As New ADODB.Recordset Dim j As Integer On Error GoTo ErrHandler 'Open two connections against the same database, 'both using row locking mode; 'Jet OLEDB:Database Locking Mode info - '0 is "page mode", '1 is "row mode" 'Open first connection to TestRowLocking.mdb. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _     "Source=c:\Programming Access\Chap10\TestRowLocking.mdb;" & _     "Jet OLEDB:Database Locking Mode=1;" 'Open second connection to TestRowLocking.mdb. cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=c:\Programming Access\Chap10\TestRowLocking.mdb;" & _     "Jet OLEDB:Database Locking Mode=1;" 'Open a recordset in the first connection and 'begin editing the first row. rs.Open "TestRowLocking", cnn1, adOpenKeyset, _     adLockPessimistic, adCmdTableDirect 'Edit first field value. Debug.Print rs.Fields("col1") rs.Fields("col1") = 2 'rs2.Update; putting an update here would 'close the record lock Debug.Print rs.Fields("col1") 'Open a recordset in the second connection. rs2.Open "TestRowLocking", cnn2, adOpenKeyset, _     adLockPessimistic, adCmdTableDirect 'Attempt to edit the first row; even in row locking, 'since we are in the pessimistic 'locking mode, this will fail. Debug.Print rs2.Fields("col1") rs2.Fields("col1") = 3 Debug.Print rs2.Fields("col1") 'Move to another row in the same page. rs2.MoveNext 'Attempt to edit the next row; this works under 'the row locking mode because the lock only applies 'to a single row, not the whole page. Debug.Print rs2.Fields("col1") 'Should not fail (row locking) rs2.Fields("col1") = 4 Debug.Print rs2.Fields("col1") 'Update recordsets. rs.Update rs2.Update 'Close connections and exit. cnn1.Close cnn2.Close Set cnn1 = Nothing Set cnn2 = Nothing Exit Sub ErrHandler: For j = 0 To cnn1.Errors.Count - 1     Debug.Print "Errors from cnn1 connection"     Debug.Print "Conn Err Num : "; cnn1.Errors(j).Number     Debug.Print "Conn Err Desc: "; cnn1.Errors(j).Description Next j For j = 0 To cnn2.Errors.Count - 1     Debug.Print "Errors from cnn2 connection"     Debug.Print "Conn Err Num : "; cnn2.Errors(j).Number     Debug.Print "Conn Err Desc: "; cnn2.Errors(j).Description Next j Resume Next End Sub 

Notice that the second connection's attempt to write to the first record in TestRowLocking.mdb fails because the first connection still has the first record open. This attempt can succeed if the first connection unlocks the record (by, for instance, running a rs.Update statement). The second connection's attempt to write to the second record succeeds immediately since there are no locks on that record. The error-handling logic displays the entries from the Jet connection's Errors collection.

Page-Level Locking

The following is an excerpt from a similarly designed procedure that uses page locking instead of record locking. Notice that the Database Locking Mode value, which was 1 in the preceding sample, is 0 in this one. These values denote, respectively, row-level and page-level tracking. Both attempts by the second connection to update records fail because the first connection has a lock open and all the records for this short table fit on a single page.

'Open first connection to TestRowLocking.mdb. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _     "Source=c:\Programming Access\Chap10\TestRowLocking.mdb;" & _     "Jet OLEDB:Database Locking Mode=0;" 'Open second connection to TestRowLocking.mdb. cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=c:\Programming Access\Chap10\TestRowLocking.mdb;" & _     "Jet OLEDB:Database Locking Mode=0;" 'Open a recordset in the first connection and 'begin editing the first row. rs.Open "TestRowLocking", cnn1, adOpenKeyset, _     adLockPessimistic, adCmdTableDirect 'Edit first field value. Debug.Print rs.Fields("col1") rs.Fields("col1") = 2 Debug.Print rs.Fields("col1") 'Open a recordset in the second connection. rs2.Open "TestRowLocking", cnn2, adOpenKeyset, _     adLockPessimistic, adCmdTableDirect 'Attempt to edit the first row; because of 'pessimistic page locking mode setting, this fails. Debug.Print rs2.Fields("col1") rs2.Fields("col1") = 3 Debug.Print rs2.Fields("col1") 'Move to another row in the same page. rs2.MoveNext 'Attempt to edit the next row; this fails too because 'the next row is on the same page. Debug.Print rs2.Fields("col1")  rs2.Fields("col1") = 4 Debug.Print rs2.Fields("col1") 'Update recordsets. rs.Update rs2.Update