5.7.1.10. Lock Probes

The lock probes are called whenever an external lock is requested by MySQL for a table using the corresponding lock mechanism on the table as defined by the table's engine type. There are three different types of lock, the read lock, write lock, and unlock operations. Using the probes you can determine the duration of the external locking routine (that is, the time taken by the storage engine to implement the lock, including any time waiting for another lock to become free) and the total duration of the lock/unlock process.

handler-rdlock-start(database, table)handler-rdlock-done(status)handler-wrlock-start(database, table)handler-wrlock-done(status)handler-unlock-start(database, table)handler-unlock-done(status)

handler-rdlock-start: Triggered when a read lock is requested on the specified database and table.
handler-wrlock-start: Triggered when a write lock is requested on the specified database and table.
handler-unlock-start: Triggered when an unlock request is made on the specified database and table.
handler-rdlock-done: Triggered when a read lock request completes. The status is 0 if the lock operation succeeded, or >0 on failure.
handler-wrlock-done: Triggered when a write lock request completes. The status is 0 if the lock operation succeeded, or >0 on failure.
handler-unlock-done: Triggered when an unlock request completes. The status is 0 if the unlock operation succeeded, or >0 on failure.

You can use arrays to monitor the locking and unlocking of individual tables and then calculate the duration of the entire table lock using the following script:

#!/usr/sbin/dtrace -s#pragma D option quietmysql*:::handler-rdlock-start{   self->rdlockstart = timestamp;   this->lockref = strjoin(copyinstr(arg0),strjoin("@",copyinstr(arg1)));   self->lockmap[this->lockref] = self->rdlockstart;   printf("Start: Lock->Read   %s.%s\n",copyinstr(arg0),copyinstr(arg1));}mysql*:::handler-wrlock-start{   self->wrlockstart = timestamp;   this->lockref = strjoin(copyinstr(arg0),strjoin("@",copyinstr(arg1)));   self->lockmap[this->lockref] = self->rdlockstart;   printf("Start: Lock->Write  %s.%s\n",copyinstr(arg0),copyinstr(arg1));}mysql*:::handler-unlock-start{   self->unlockstart = timestamp;   this->lockref = strjoin(copyinstr(arg0),strjoin("@",copyinstr(arg1)));   printf("Start: Lock->Unlock %s.%s (%d ms lock duration)\n",          copyinstr(arg0),copyinstr(arg1),          (timestamp - self->lockmap[this->lockref])/1000000);}mysql*:::handler-rdlock-done{   printf("End:   Lock->Read   %d ms\n",          (timestamp - self->rdlockstart)/1000000);}mysql*:::handler-wrlock-done{   printf("End:   Lock->Write  %d ms\n",          (timestamp - self->wrlockstart)/1000000);}mysql*:::handler-unlock-done{   printf("End:   Lock->Unlock %d ms\n",          (timestamp - self->unlockstart)/1000000);}

When executed, you should get information both about the duration of the locking process itself, and of the locks on a specific table:

Start: Lock->Read   test.t2End:   Lock->Read   0 msStart: Lock->Unlock test.t2 (25743 ms lock duration)End:   Lock->Unlock 0 msStart: Lock->Read   test.t2End:   Lock->Read   0 msStart: Lock->Unlock test.t2 (1 ms lock duration)End:   Lock->Unlock 0 msStart: Lock->Read   test.t2End:   Lock->Read   0 msStart: Lock->Unlock test.t2 (1 ms lock duration)End:   Lock->Unlock 0 msStart: Lock->Read   test.t2End:   Lock->Read   0 ms