Oracle 10.2 - Statement Lock

ODBI=2 (READ_COMMITTED)
FOR UPDATE WAIT n (X_DBMENV.DAT)

The lock timeout is only set for SELECT operations, and then only for reads before UPDATE and DELETE. it is not possible to put the lock timeout on other SELECTs because a lock is applied too which is undesirable for all SELECTS. Only specific ones should be locked. INSERTS do not provide a WAIT option and thus wait for the row to be unlocked (block).

WITH_KEY IO access

 

Step

Action (User)

IO$STS

Message

1

Insert (1)

OK

IO Operation Succeeded

2

Insert (2)

Block

 

3

Delete (2)

NR

Record not found

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)

OK

IO Operation Succeeded

9

Commit (1)

 

 

10

Delete (1)

OK

IO Operation Succeeded

11

Insert (2)

Block

 

12

Update (2), Delete (2)

ER

Record Locked

13

Commit (1)

 

 

 

 

Note the highlighted lines 2 and 11 differ from Lock Timeout results for Oracle. For the inserts, it's not possible to set a timeout, so they block until the transaction is committed.

Last Record Read IO access

Same results as for WITH_KEY in terms of how using FOR UPDATE differs from a LockTimeout.

Step

Action (User)

IO$STS

Message

1

Insert (1)

OK

IO Operation Succeeded

2

Insert (2)

Block

 

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)

OK

IO Operation Succeeded

9

Commit (1)

 

 

10

Fetch (1), Fetch (2)

OK

IO Operation Succeeded

11

Delete (1)

OK

IO Operation Succeeded

12

Insert (2)

Block

 

13

Fetch (2) Update (2), Fetch (2) Delete (2)

ER

Record Locked

14

Commit (1)

 

 

 

 

The conclusion drawn from this testing is that if Oracle is being used on Linux and Windows then use the FOR UPDATE option on BOTH platforms. Otherwise use a LockTimeout. And even so, if different databases are used, be very wary that behavior is different for different databases and some database specific code may be required in some situations.