ロックの確認

どのテーブルがロックされているかを確認する方法

方法１：SHOW OPEN TABLESで確認

参考：http://d.hatena.ne.jp/mir/20070626/p2

方法２：

mysqldへdebugオプションを発行して、エラーログを確認する。

client 1

mysqladmin -uroot debug

client 2

mysql> lock tables t1 write;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables;
+----------+--------------+--------+-------------+
| Database | Table        | In_use | Name_locked |
+----------+--------------+--------+-------------+
| mysql    | procs_priv   |      0 |           0 |
| mysql    | db           |      0 |           0 |
| mysql    | host         |      0 |           0 |
| mysql    | tables_priv  |      0 |           0 |
| mysql    | user         |      0 |           0 |
| mysql    | columns_priv |      0 |           0 |
| test     | t1           |      1 |           0 |
+----------+--------------+--------+-------------+
7 rows in set (0.00 sec)

client 1

[root@h1 mysql]# more h1.err
070619 16:58:33  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
070619 16:58:33  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
070619 16:58:33  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
070619 16:58:33  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
070619 16:58:34  InnoDB: Started; log sequence number 0 0
070619 16:58:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.42-enterprise-gpl'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server (GPL)

Status information:

Current dir: /var/lib/mysql/
Running threads: 1  Stack size: 262144
Current locks:
lock: 0x1151fa0:

lock: 0x114c4a0:

lock: 0x11474f0:

lock: 0x1138cd0:

lock: 0x1134cf0:

lock: 0x112eca0:


Key caches:
default
Buffer_size:       8388600
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             0
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              0
reads:                   0


handler status:
read_key:            0
read_next:           0
read_rnd             0
read_first:          3
write:               0
delete               0
update:              0

Table status:
Opened tables:         12
Open tables:            6
Open files:            12
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 1
Next alarm time: 0

Memory status:
Non-mmapped space allocated from system: 6029312
Number of free chunks:                   4
Number of fastbin blocks:                0
Number of mmapped regions:               12
Space in mmapped regions:                23711744
Maximum total allocated space:           0
Space available in freed fastbin blocks: 0
Total allocated space:                   5934032
Total free space:                        95280
Top-most, releasable space:              91328
Estimated memory (with thread stack):    30003200


Status information:

Current dir: /var/lib/mysql/
Running threads: 2  Stack size: 262144
Current locks:
lock: 0x1169e00: write
write     : 0x113cda8 (1147701600:10);

lock: 0x1151fa0:

lock: 0x114c4a0:

lock: 0x11474f0:

lock: 0x1138cd0:

lock: 0x1134cf0:

lock: 0x112eca0:


Key caches:
default
Buffer_size:       8388600
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             0
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              0
reads:                   0


handler status:
read_key:            0
read_next:           0
read_rnd             0
read_first:          3
write:              14
delete               0
update:              0

Table status:
Opened tables:         14
Open tables:            7
Open files:            14
Open streams:           0

Alarm status:
Active alarms:   1
Max used alarms: 2
Next alarm time: 28752

Thread database.table_name          Locked/Waiting        Lock_type

5       test.t1                     Locked - write        High priority write lock

方法３：INFORMATION_SCHEMAで実装予定の機能を使う。

MySQL Bugs: #23076: No way of viewing locks from within the server


方法２の補足：
ソース上は、こんな感じらしい。
./sql/sql_test.cc fileのmysql_print_status().

void mysql_print_status()
{
char current_dir[FN_REFLEN];
STATUS_VAR tmp;

calc_sum_of_all_status(&tmp);
printf("\nStatus information:\n\n");
VOID(my_getwd(current_dir, sizeof(current_dir),MYF(0)));
printf("Current dir: %s\n", current_dir);
printf("Running threads: %d Stack size: %ld\n", thread_count,
(long) thread_stack);
thr_print_locks(); // Write some debug info
#ifndef DBUG_OFF
print_cached_tables();
#endif
/* Print key cache status */
puts("\nKey caches:");
process_key_caches(print_key_cache_status);
pthread_mutex_lock(&LOCK_status);
printf("\nhandler status:\n\
read_key: %10lu\n\
read_next: %10lu\n\
read_rnd %10lu\n\
read_first: %10lu\n\
write: %10lu\n\
delete %10lu\n\
update: %10lu\n",
tmp.ha_read_key_count,
tmp.ha_read_next_count,
tmp.ha_read_rnd_count,
tmp.ha_read_first_count,
tmp.ha_write_count,
tmp.ha_delete_count,
tmp.ha_update_count);
pthread_mutex_unlock(&LOCK_status);
printf("\nTable status:\n\
Opened tables: %10lu\n\
Open tables: %10lu\n\
Open files: %10lu\n\
Open streams: %10lu\n",
tmp.opened_tables,
(ulong) cached_tables(),
(ulong) my_file_opened,
(ulong) my_stream_opened);

ALARM_INFO alarm_info;
#ifndef DONT_USE_THR_ALARM
thr_alarm_info(&alarm_info);
printf("\nAlarm status:\n\
Active alarms: %u\n\
Max used alarms: %u\n\
Next alarm time: %lu\n",
alarm_info.active_alarms,
alarm_info.max_used_alarms,
alarm_info.next_alarm_time);
#endif
display_table_locks();
fflush(stdout);
my_checkmalloc();
TERMINATE(stdout); // Write malloc information

#ifdef HAVE_MALLINFO
struct mallinfo info= mallinfo();
printf("\nMemory status:\n\
Non-mmapped space allocated from system: %d\n\
Number of free chunks: %d\n\
Number of fastbin blocks: %d\n\
Number of mmapped regions: %d\n\
Space in mmapped regions: %d\n\
Maximum total allocated space: %d\n\
Space available in freed fastbin blocks: %d\n\
Total allocated space: %d\n\
Total free space: %d\n\
Top-most, releasable space: %d\n\
Estimated memory (with thread stack): %ld\n",
(int) info.arena ,
(int) info.ordblks,
(int) info.smblks,
(int) info.hblks,
(int) info.hblkhd,
(int) info.usmblks,
(int) info.fsmblks,
(int) info.uordblks,
(int) info.fordblks,
(int) info.keepcost,
(long) (thread_count * thread_stack + info.hblkhd + info.arena));
#endif
puts("");
}