CODEWITHSUNDEEP
mysqllockinginnodbblock
WilliamLou
WilliamLou

Reputation: 1904

MySQL InnoDB lock question

I have a question about MySQL InnoDB. For example: I have the following table created:

   mysql>CREATE TABLE IF NOT EXISTS `SeqNum`
   (
     `id` varchar(10) NOT NULL,
     `seq_num` BIGINT(30) default 0,
      PRIMARY KEY(`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  Query OK, 0 rows affected (0.00 sec)

  mysql>INSERT IGNORE INTO `SeqNum` VALUES('current',0);
  Query OK, 1 rows affected (0.00 sec)

Now, I have two mysql connections to the same database, I name them as Thread A and B. In thread A, I have the following SQL statement:

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

    mysql> select `seq_num` from SeqNum where `id`='current' FOR UPDATE;
       +---------+
       | seq_num |
       +---------+
       |       0 | 
       +---------+
       1 row in set (0.01 sec)

and then, I just leave the thread A as it is.

In thread B, I would like to do the same query:

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

   mysql>SELECT `current_seq_num` FROM SeqNum WHERE `id` = 'current' FOR UPDATE;

thread B will throw an MySQL 1205 Error after the lock waiting time out: Lock wait timeout exceeded; try restarting transaction.

It makes sense, because threadA put a 'X' lock on that row, so that thread B can NOT get the 'X' lock until thread A release the lock.

Now, my question is: from the perspective of thread B, how could I know which thread/connection block my request (to obtain the 'UPDATE' privilege for the table 'SeqNum') when MySQL return Error 1205 to me? If threadA is doing nothing after it obtains the X lock, and I run 'show processlist' in thread B, all I have are: several threads with 'Sleep' Status (I assume there are more than two threads connected to the datbase), I can NOT identify which thread blocked my request?

Hopefully, I explained the question clearly. Thanks!

Upvotes: 4

Views: 4874

Answers (4)

Rituparna Kashyap
Rituparna Kashyap

Reputation: 1507

InnoDB plugin will give you a clear picture of the locked and the locking queries.

For instance 

SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
      r.trx_query waiting_query,
      b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
      b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id;

will give you a locking and blocking transactions. Only you have to have innodb plugin installed.

Upvotes: 7

WilliamLou
WilliamLou

Reputation: 1904

I think at this stage, the query "SHOW ENGINE INNODB STATUS\G" could solve my problem. Here is the info from MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html#innodb-standard-monitor

run this query in thread B, you will get the thread which blocked your request in "transaction" section

Upvotes: 2

Morgan Tocker
Morgan Tocker

Reputation: 3438

You can only easily tell if you use a newer (InnoDB Plugin) release. There are some tables in information_schema which you can query:

  • SELECT * FROM information_schema.innodb_trx;
  • SELECT * FROM information_schema.innodb_locks;

In the innodb_trx table there should be a column called 'trx_mysql_thread_id' (or similar - it is trx_mysql_thread_id in MySQL 5.5). That is the id in SHOW PROCESSLIST.

(Note that innodb_locks is misnamed. It will only be populated for lock-waits, not locks).

Upvotes: 4

Adrian Smith
Adrian Smith

Reputation: 17553

Are you in a transaction on both sessions, i.e. have you typed

START TRANSACTION

Normally the behaviour would be as you desire, i.e. a FOR UPDATE would just block until the lock were available (due to COMMIT or ROLLBACK from the first transaction)

Upvotes: 0

Related Questions