Oracle 10 2 Statement Lock

LANSA Technical

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. Its 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.

previous page start next page