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