First page Back Continue Last page Graphics
Locking Issues: Blocking
One session holds lock, causes other sessions to wait
Four statements cause blocking (waiting)
- UPDATE and DELETE
- FIX: SELECT FOR UPDATE NOWAIT before UPDATE
- SELECT FOR UPDATE
- FIX: Add the NOWAIT clause
- INSERT
- Only when simultaneous insertion of duplicate primary key
- FIX: Use SEQUENCEs to eliminate chance of duplicate keys
Notes:
“Blocking” describes the situation where one session holds a lock and another session hangs (waits) until the first session commits or rolls back, releasing the lock. This can occur during INSERT, UPDATE , DELETE and SELECT FOR UPDATE operations.
INSERT operations will only cause blocking if a primary key or unique constraint exists and two sessions attempt to simultaneously try to insert a duplicate key value. Use an Oracle sequence to eliminate duplicate key values.
SELECT FOR UPDATE statements will hang if the row is already locked. Simply add the NOWAIT option to cause Oracle to return an error indicating the row is locked.
UPDATE and DELETE statements will hang if the operation affects rows already locked by an UPDATE or DELETE. This is probably similar to the lost update problem. Use one of the solutions presented for lost updates.