Could please help us in understanding better on queries could be written in different order to the binary log breaking locking semantics and contrary to serialization before MySQL 5.5.3

Thanks,
Amar

If you follow my test case you will see that I have started a transaction and issued a SELECT query and not a query that would hold any row lock. The point was to show the implications of a transaction not holding metadata locks on tables that have been queried in the transaction, which causes problems by breaking isolation.

However, in your case what you are doing is that by executing an update query on the table, the transaction holds row locks on row with the id=3, which is what is preventing the ALTER from going through. So you are facing a different scenario in that you have row locks blocking the ALTER, while the post deals with metadata locks. If you can enable the InnoDB plugin on your 5.1 installation instead of using the built-in InnoDB, then you can further clarify to yourself that the ALTER in your test case is waiting on InnoDB row locks by setting innodb_lock_wait_timeout to 1 and then issuing the ALTER:

— session 1
start transaction;
select * from t1 where id=3 for update;

— session 2
set innodb_lock_wait_timeout=1;
alter table t1 engine=innodb;

And the alter will timeout after approximately a second with the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set id=30 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
alter table t1 add column title2 varchar(30) default ‘ook’; (hang)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table t1 add column title2 varchar(30) default ‘ook’;
Query OK, 3 rows affected (24.20 sec)
Records: 3 Duplicates: 0 Warnings: 0

Events in binlog

Info
————————————————————————
Server ver: 5.1.48-community-log, Binlog ver: 4
BEGIN
use bruce; update t1 set id=30 where id=3
COMMIT /* xid=18 */
use bruce; alter table t1 add column title3 varchar(30) default ‘oook’

As you see,alter table is logged after update,that’s correct.
How can i get the same result as you ? Is there something wrong in my steps?

Thanks, for letting me know I will test out MariaDB 5.5.29

Indeed the current version of pt-osc needs to be updated because it does not currently tackle the condition where it has to wait on metadata locks, and hence I have reported the bug: https://bugs.launchpad.net/percona-toolkit/+bug/1113301

Yes when queries are waiting for metadata locks, the thread state is “Waiting for table metadata lock” in the processlist. Probably we can have a new feature in Percona Server to tackle the issue that you face during DDLs.

I’ve encountered this issue about every time I have to do a production DDL change. After a few times fighting it, I now have a script that will kill all sleeping threads over 1 second for the database i’m working on. If not the DDL hangs on a sleeping thread, and the reads after the DDL also hang.