1.READ
Locks 一个表用于读,A READ LOCK 锁住表用于read查询比如SELECT 从表获得数据
它不允许写操作 比如INSERT,DELETE或者UPDATE 修改表数据
SESSION 1：
mysql> lock table test read;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
+------+------+
2 rows in set (0.02 sec)
mysql> insert into test values(2,'a');
ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated
mysql>
SESSON 2：
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
+------+------+
2 rows in set (0.00 sec)
mysql> insert into test values(2,'a'); --HANG
查看锁情况:
mysql> show processlist;
+----+----------+--------------+------+---------+------+------------------------------
+--------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+----------+--------------+------+---------+------+------------------------------
+--------------------------------+
| 4 | zjzc_app | localhost | zjzc | Sleep | 1150 | | NULL
|
| 5 | zjzc_app | localhost | zjzc | Sleep | 1166 | | NULL
|
| 14 | zjzc_app | master:48786 | zjzc | Query | 0 | init | show processlist
|
| 15 | zjzc_app | master:48787 | zjzc | Query | 6 | Waiting for table level lock | insert into test
values(2,'b') |
+----+----------+--------------+------+---------+------+------------------------------
+--------------------------------+
4 rows in set (0.00 sec)
查看持有锁的Id:
---TRANSACTION 3671, not started
mysql tables in use 1, locked 1
MySQL thread id 15, OS thread handle 0x7f2af0440700, query id 836 master 192.168.33.220 zjzc_app Waiting
for table level lock
insert into test values(2,'b')
---TRANSACTION 3670, not started
mysql tables in use 1, locked 1
MySQL thread id 14, OS thread handle 0x7f2af0481700, query id 1097 master 192.168.33.220 zjzc_app
cleaning up
没有SQL命令可以用来显示那些持有着表锁从而阻塞了你查询的线程，使用SHOW PROCESSLIST，可以看到
在等待锁的进程，但是看不到持有着锁的进程。
[root@master ~]# mysqladmin -uroot -p1234567 -h127.0.0.1 debug
Warning: Using a password on the command line interface can be insecure.
清空/var/log/mysql/mysqld.log ,执行mysqladmin -uroot -p1234567 -h127.0.0.1 debug
Thread database.table_name Locked/Waiting Lock_type
14 zjzc.test Locked - read Read lock without concurrent inserts
15 zjzc.test Waiting - write Concurrent insert lock
2.WRITE
Locks一个表用于写,写的锁是一个排它锁。它可能只被需要当一个表不被使用
一旦使用,只有一个客户端持有写锁能对表进行读写操作。
Session 1:
mysql> lock table test write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 2 | a |
| 2 | b |
+------+------+
4 rows in set (0.00 sec)
mysql> insert into test values(3,'c');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | a |
| 2 | a |
| 2 | b |
| 3 | c |
+------+------+
5 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Session 2:
Database changed
mysql> select * from test; ---HANG
清空/var/log/mysql/mysqld.log ,执行mysqladmin -uroot -p1234567 -h127.0.0.1 debug
Thread database.table_name Locked/Waiting Lock_type
31 zjzc.test Locked - write High priority write lock
Thread ID=31 持有的写锁
mysql> show table status like 'Notice'G
*************************** 1. row ***************************
Name: Notice
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 273
Avg_row_length: 420
Data_length: 114688
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 293
Create_time: 2015-06-17 18:35:13
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: ?????
1 row in set (0.00 sec)