SQL Server 2005

SQL Server 2005

ODBI=2 (READ_COMMITTED)LockTimeout=2 (X_DBMENV.DAT)

WITH_KEY IO access

 

Step Action (User) IO$STS Message 1 Insert (1) OK IO Operation Succeeded 2 Insert (2) ER Record Locked 3 Delete (2) ER Record Locked 4 Commit (1)     5 Update (1) OK IO Operation Succeeded 6 Update (2) ER Record Locked 7 Fetch (1) OK IO Operation Succeeded 8 Fetch (2) ER Record Locked 9 Commit (1)     10 Delete (1) OK IO Operation Succeeded 11 Insert (2) ER Record Locked 12 Update (2), Delete (2) ER Record Locked 13 Commit (1)

 

Note the differences between ASA and SQL Server at steps 2, 7 and 12. There may be many ramifications of these differences in attempting to have an application perform the same way on both databases. For example, Step 7 implies that if your application updates a record and doesn't commit it, then re-reads the record, on SQL Server it will work. When you then execute it on ASA, it will timeout if a LockTimeout is set, otherwise it will block.

Last Record Read IO access

This is a different set of steps as there must be a record to read before the last record read can be updated or deleted! Hence when an error occurs, the record must be fetched again like in Step 13.

Step Action (User) IO$STS Message 1 Insert (1) OK IO Operation Succeeded 2 Insert (2) ER Record Locked 3 Commit (1)     4 Fetch (1), Fetch (2) OK IO Operation Succeeded 5 Update (1) OK IO Operation Succeeded 6 Update (2) ER Record Locked 7 Fetch (1) OK IO Operation Succeeded 8 Fetch (2) ER Record Locked 9 Commit (1)     10 Fetch (1), Fetch (2) OK IO Operation Succeeded 11 Delete (1) OK IO Operation Succeeded 12 Insert (2) ER Record Locked 13 Fetch (2) Update (2), Fetch (2) Delete (2) ER Record Locked 14 Commit (1)

Note that step 13 gets the lock timeout on the Fetch not the Update. Compare this with ASA which reports the row does not exist on the Fetch and Oracle which succeeds on the Fetch but gets the lock timeout on the Update.