1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select Statement Helds lock on MySQL slave machine

Discussion in 'MySQL Forums' started by prashant, Nov 25, 2013.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hello,

    I have observed a strange behavior on our MySQL Master-Slave Setup.

    MySQL version: 5.5.28 for both master and slave. Slave read-only is set to true.

    1. I ran following SQL on Master

    Code (SQL):

    mysql> CREATE TABLE test(i INTEGER);
    Query OK, 0 ROWS affected (0.11 sec
    mysql> INSERT INTO test VALUES (2);
    Query OK, 1 ROW affected (0.00 sec)

    mysql> commit;
    Query OK, 0 ROWS affected (0.05 sec)

    mysql> SELECT * FROM test;

    +------+
    | i    |
    +------+
    |    2 |
    +------+
    1 ROW IN SET (0.00 sec)


     

    2. On Slave machine, I see the same data as of now

    Code (SQL):
    mysql> SELECT * FROM test;

    +------+
    | i    |
    +------+
    |    2 |
    +------+
    1 ROW IN SET (0.00 sec)

    3. Now I drop the table from Master

    Code (SQL):
    mysql> DROP TABLE test;
    Query OK, 0 ROWS affected (0.05 sec)
    4. This does not get replicated on Slave

    Code (SQL):
    mysql> SELECT * FROM test;

    +------+
    | i    |
    +------+
    |    2 |
    +------+
    1 ROW IN SET (0.00 sec)
    5. I Checked show process list on slave, and found that a lock is being held on this table
    Code (SQL):

    mysql> SHOW processlist;
    +----+-----------------+-----------+-------------+---------+------+----------------------------------+-------------------------------------------------+
    | Id | USER            | Host      | db          | Command | TIME | State                            | Info                                            |
    +----+-----------------+-----------+-------------+---------+------+----------------------------------+-------------------------------------------------+
    |  1 | system USER     |           | NULL        | CONNECT |  487 | Waiting FOR master TO send event | NULL                                            |
    |  2 | system USER     |           | emslite_new | CONNECT |  -90 | Waiting FOR TABLE metadata LOCK  | DROP TABLE `test` /* generated by server */ |
    |  3 | event_scheduler | localhost | NULL        | Daemon  |  487 | Waiting ON empty queue           | NULL                                            |
    |  5 | root            | localhost | NULL        | Query   |    0 | NULL                             | SHOW processlist                                |
    +----+-----------------+-----------+-------------+---------+------+----------------------------------+-------------------------------------------------+
    4 ROWS IN SET (0.00 sec)

    6. I commit my current slave session or exit the same, this gets replicated.

    Code (SQL):
    mysql> commit;
    Query OK, 0 ROWS affected (0.06 sec)

    mysql> SELECT * FROM test;
    ERROR 1146 (42S02): TABLE 'test' doesn't exist
    Can someone please explain why a select statement held lock on test table at Slave? A commit after the select released the lock and the change was replicated successfully.

    Best Regards,
    Prashant
     
  2. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    I just got more information about this. In MySQL select statement internally starts a transaction and will acquire a metadata lock on the table which is selected. Thus a simple read on the table will acquire a lock and one cannot perform a DDL on that table.

    I dont understand whats the point of having such an implementation in the database.

    http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

    Best Regards,
    Prashant