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.

1. create table a (x number); (and equivalent in MySQL for InnoDB)
2. Session A: insert into a values (9);
3. Session A: commit;
4. Session A: start transaction (in mysql)
5. Session A: select * from a where x=9 for update;
6. Session B: start transaction (in mysql)
7. Session B: select * from a where x=9 for update;
8. Session B hangs, waiting for row-level lock to be release from the row by Session A
9. Session A: update a set x=4;
10. Session A: commit;
11. At this point, Session B returns no rows. Lock has been released, and row no longer confirms to where, so is not returned.
12. 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:

1. 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.
2. 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 dispite 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.

From our Oracle test instance:

1360965 rows created.
Elapsed: 00:01:17.90

That's 1¼ minutes to insert (and index) over 1¼ million rows.

And this is a very old test instance. I think the hardware was last updated 2-3 years ago.

That's pretty quick!

Database error messages in general are very bad. Why?

Oracle Version 8 had lots of messages such as "Invalid column name" where they meant:

1. Column name not found in the table in question. The word "invalid" is the wrong word as it implies illegal characters or something like that.
2. Which column? Which table? The parser surely knows this at the time it generates the error message. But it helpfully chooses not to inform the user.

Thankfully Oracle 10 has improved its error messages a lot. They include the statement in question and the point in the statement producing the problem. And error messages contain which foreign key constraint has been violated, and so on.

But I have the following problem with MySQL. I try to create a table with InnoDB with a foreign key constraint and it says:

ERROR 1005 (HY000):
Can't create table './myschema/mytable.frm' (errno: 150)

What it means is: the statement has an error in it. But what is the error? In this case, there was a foreign key constraint and the column didn't exist in the referenced table. But why couldn't it tell me this?