Friday, December 19, 2008

Brain Damaged Schema Locking

I have no clue what the motivation is behind mysql's schema level locking algorithm.  It seems to lock things when it doesn't need to but doesn't when it needs to.  These comments apply to versions 5.1 and earlier.

Case in point:  a table can be dropped while a transaction that updated it is still open.

This is a big problem for replication.  It stops replication in its tracks immediately.  Once the open transaction COMMITs, its SQL gets written to the binary log.  The problem is that the DROP TABLE gets written immediately to the binary log.  So the DROP TABLE is in the binary log before INSERT/UPDATE/etc. statements manipulating the table.  Needless to say, replication dies with a nonexistant table error when the latter statements are encountered by the slave.

Why doesn't the schema locking algorithm block the DROP TABLE until the open transaction terminates?  That's what would make sense to me.  (Psst, that's the way Oracle works.)

(This problem with DROP TABLE is a MySql bug reported in 2003!)

No comments: