MySQL Locking tables details

MySQL allows a client session to acquire a table lock explicitly for preventing other sessions from accessing the table during a specific period. A client session can acquire or release table locks only for itself. It cannot acquire or release table locks for other sessions.

Below are the method to check lock details:

SHOW ENGINE INNODB STATUS
        This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.

Enable InnoDB Lock Monitor (enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor)
        This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output, but it can get truncated too.

  Run "mysqladmin debug"
        Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table, because LOCK TABLE is external to the InnoDB storage engine. Not so great because a bit cryptic to read, and I wouldn't solely reply on it, as it's often most helpful in conjuntion with other details).

SHOW FULL PROCESSLIST
        This will show all connected threads. Specifically, when it comes to 'hidden' locks, it would show a user that has been connected, but idle (but who could have issued a LOCK TABLE command).

Error log
        Of course, always check out the error log for messages and/or anything out of the ordinary. (Not to mention extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor.)
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables
        This is only available as of MySQL 5.1 and newer (and you must be using the InnoDB Plugin), but since most of you will be running this, they contain great information. In fact, I've written a past post on Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables which contains even more details on using these 3 tables for troubleshooting.

SHOW CREATE TABLE outputs for each table involved

Useful Queries 

We can use INFORMATION_SCHEMA Tables.

To check about all the locks transactions are waiting for -

SELECT * FROM INNODB_LOCK_WAITS

A list of blocking transactions

SELECT * FROM INNODB_LOCKS  WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS)
SELECT INNODB_LOCKS.*  FROM INNODB_LOCKS JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID)

A List of locks on perticular table:

SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name

A list of transactions waiting for locks:

SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE = 'LOCK WAIT'

Other Queries:

-> show open tables WHERE In_use > 0;
-> SHOW OPEN TABLES WHERE `Table` LIKE '%Table Name%' AND `Database` LIKE 'Data BaseName' AND In_use > 0;

http://www.chriscalender.com/advanced-innodb-deadlock-troubleshooting-what-show-innodb-status-doesnt-tell-you-and-what-diagnostics-you-should-be-looking-at/

http://www.xaprb.com/blog/2006/07/31/how-to-analyze-innodb-mysql-locks/

http://www.slideshare.net/valeriikravchuk1/understanding-innodb-locks-and-deadlocks

IOPS measurement

IOPS (input/output operations per second) is the standard unit of measurement for the maximum number of reads and writes to non-contiguous storage locations of devices like hard disk drives (HDD), solid state drives (SSD), and storage area networks (SAN). IOPs is often measured with an open source network testing tool called an Iometer.

Some commonly accepted averages for random IO operations, calculated as 1/(seek + latency) = IOPS:

 IOPS calculations

Every disk in your storage system has a maximum theoretical IOPS value that is based on a formula. Disk performance — and IOPS — is based on three key factors:

• Rotational speed (aka spindle speed). Measured in revolutions per minute (RPM), most disks you'll consider for enterprise storage rotate at speeds of 7,200, 10,000 or 15,000 RPM with the latter two being the most common. A higher rotational speed is associated with a higher performing disk. This value is not used directly in calculations, but it is highly important. The other three values depend heavily on the rotational speed, so I've included it for completeness.
• Average latency. The time it takes for the sector of the disk being accessed to rotate into position under a read/write head.
• Average seek time. The time (in ms) it takes for the hard drive's read/write head to position itself over the track being read or written. There are both read and write seek times; take the average of the two values.

To calculate the IOPS range, use this formula: Average IOPS: Divide 1 by the sum of the average latency in ms and the average seek time in ms (1 / (average latency in ms + average seek time in ms).