A default run of mysqlhotcopy results in the below activity in the mysql log, as followed by mysql-tail
130516 12:03:24 2400342 Connect root@localhost on information_schema
2400342 Query set autocommit=1
2400342 Query show variables like 'datadir'
2400342 Query SHOW TABLES FROM `tmp_offline_db`
2400342 Query LOCK TABLES `tmp_offline_db`.`CableMap` READ,
`tmp_offline_db`.`CableMapVld` READ,
...
2400342 Query FLUSH TABLES /\*!32323 `tmp_offline_db`.`CableMap`,
`tmp_offline_db`.`CableMapVld`,
...
130516 12:03:25 2400342 Query UNLOCK TABLES
2400342 Quit
Checking the /usr/bin/mysqlhotcopy perl script source
The official docs are not very explicit, but imply:
- with a READ lock, any connection can read from the table, but no connection can write to the table
lock tables <name> READ
A table lock protects only against inappropriate reads or writes by other sessions. The session holding the lock, even a read lock, can perform table-level operations such as DROP TABLE.
READ locks mean :
- The session that holds the lock can read the table (but not write it).
- Multiple sessions can acquire a READ lock for the table at the same time.
- Other sessions can read the table without explicitly acquiring a READ lock.
WRITE lock
- The session that holds the lock can read and write the table.
- Only the session that holds the lock can access the table. No other session can access it until the lock is released.
- Lock requests for the table by other sessions block while the WRITE lock is held.
flush tables ensures whats is on disk is the latest state of the tables
- http://dev.mysql.com/doc/refman/5.0/en/flush.html
- Closes all open tables, forces all tables in use to be closed, and flushes the query cache
From the first session:
mysql> CREATE TABLE t1 (ID INT NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t1 VALUES (101);
Query OK, 1 row affected (0.00 sec)
mysql> LOCK TABLE t1 READ;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (102);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
mysql> LOCK TABLE t1 WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (102);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (103);
Query OK, 1 row affected (0.00 sec)
mysql> /* at this point startup another session and do a select, its blocked */
mysql> LOCK TABLE t1 READ ;
Query OK, 0 rows affected (0.00 sec)
mysql> /* over in the other session downgrading the lock from WRITE to READ allowed the select to complete */
mysql> /* an insert from the other session is hanging around blocked */
mysql> UNLOCK TABLES ;
Query OK, 0 rows affected (0.00 sec)
mysql> /* the insert from the other session proceeded when the unlock was done */
Other session:
mysql> select * from t1 ;
+-----+
| ID |
+-----+
| 101 |
| 102 |
| 103 |
+-----+
3 rows in set (1 min 9.29 sec)
mysql> INSERT INTO t1 VALUES (201);
Query OK, 1 row affected (3 min 40.96 sec)
Checking processlist while one session is locked out, and has hung around for 2607 seconds:
mysql> show processlist ;
+---------+------+-------------------------+----------------+---------+------+--------+-----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+------+-------------------------+----------------+---------+------+--------+-----------------------------+
| 2400340 | root | belle7.nuu.edu.tw:34227 | tmp_offline_db | Sleep | 2657 | | NULL |
| 2400343 | root | belle7.nuu.edu.tw:34148 | tmp_offline_db | Query | 0 | NULL | show processlist |
| 2400344 | root | belle7.nuu.edu.tw:49003 | tmp_offline_db | Query | 2607 | Locked | INSERT INTO t1 VALUES (301) |
+---------+------+-------------------------+----------------+---------+------+--------+-----------------------------+
3 rows in set (0.00 sec)
On accidentally closing the session that held the read lock the lock was releases and the other session proceeded with the insert after 46min of hanging around:
mysql> INSERT INTO t1 VALUES (301);
Query OK, 1 row affected (46 min 16.92 sec)
mysql> select @@hostname ;
+-------------------+
| @@hostname |
+-------------------+
| belle7.nuu.edu.tw |
+-------------------+
1 row in set (0.00 sec)
mysql> show variables like '%timeout' ;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+----------------------------+-------+
10 rows in set (0.00 sec)
Apparently table_lock_wait_timeout is unused, http://bugs.mysql.com/bug.php?id=41949 by observation the locks wait around much longer then 50s
The effective timeout maybe wait_timeout which defaults to 8 hrs * http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_wait_timeout
mysql> select @@hostname, @@wait_timeout/60/60 ;
+-------------------+----------------------+
| @@hostname | @@wait_timeout/60/60 |
+-------------------+----------------------+
| belle7.nuu.edu.tw | 8.00000000 |
+-------------------+----------------------+
1 row in set (0.00 sec)
External locking is the use of file system locking to manage contention for MyISAM database tables by multiple processes.
With external locking disabled, to use myisamchk, you must either stop the server while myisamchk executes or else lock and flush the tables before running myisamchk. To avoid this requirement, use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables.
mysql> show variables like '%locking' ;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| skip_external_locking | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> select @@hostname ;
+-------------------+
| @@hostname |
+-------------------+
| belle7.nuu.edu.tw |
+-------------------+
1 row in set (0.00 sec)