Difference between row-level locking algorithms on Oracle and MySQL InnoDB
I know the rules for Oracle row locking well. A row can be locked for write if one updates it, or if one "select for update"s it.
- create table a (x number); (and equivalent in MySQL for InnoDB)
- Session A: insert into a values (9);
- Session A: commit;
- Session A: start transaction (in MySQL)
- Session A: select * from a where x=9 for update;
- Session B: start transaction (in MySQL)
- Session B: select * from a where x=9 for update;
- Session B hangs, waiting for row-level lock to be release from the row by Session A
- Session A: update a set x=4;
- Session A: commit;
- At this point, Session B returns no rows. Lock has been released, and row no longer confirms to where, so is not returned.
- Session A: update a set x=5;
This is where the difference occurs:
- Oracle returns the row to session A. The command "for update" in session B did not return any rows, and thus no rows were locked, and thus session A has nothing to wait on.
- MySQL (version 4.1.18) blocks session A waiting on the transaction in session B to be ended. That means A requires a lock owned by B. But what is this lock? Is it a row-level lock on the row which was not selected? Some other type of lock?
The reason I tested this was it just occurred to me that in a "select for update" the where clause is applied twice:
- Firstly to determine which rows to return. A lock is requested for those rows, which can involve waiting until the lock is released if it is owned by some other transaction.
- After the lock has been acquired, the rows is checked again to see if it still confirms to the where clause. If not, it is not returned (although the session blocked waiting for it)
I suspect that both databases simply do step 1 involving waiting and acquiring the lock on the rows which initially conform to the where clause. And both apply the where in step 2, as in both cases no rows were actually returned despite the wait. But Oracle, upon noticing the row will not be returned, actually releases the lock as the command implies only rows returned will be locked. But MySQL just leaves the lock form step 1 hanging around, which is wrong.