mysql中myisam锁

mysql中myisam锁问题:
1.myisam表锁状态参数
mysql> show status like "table%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 249 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.01 sec)
#如果table_locks_waited的值比较高，则说明存在着较为严重的表级锁争用情况.
2.mysql锁分为两种模式:
1)table read lock 表共享读锁
2)table write lock 表独占写锁
可见，对myisam表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同表的写请求，对myisam表的写操作，则会阻塞其他用户对同一表的读和写操作；myisam表的读操作和写操作之间，以及写操作之间是串行的
3.表锁测试
首先测试读锁:
1) mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2) mysql> lock table t2 read;
Query OK, 0 rows affected (0.00 sec)
3) mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
4 rows in set (0.02 sec)
4). mysql> insert into t2 values(6);
ERROR 1099 (HY000): Table 't2' was locked with a READ lock and can't be updated
#可见对t2设置了读锁后，自己可以读这张表t2,但是term1终端不能对其有写的权限.
下面去term2上去测试:
term2上测试:
5) mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
+------+
4 rows in set (0.02 sec)
6) mysql> insert into t2 values(6);
等待...
#可见在term2上能读，但是不能写这些一直处于等待状态.
此时有term1上执行:
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
则term2上的写入操作成功
mysql> insert into t2 values(6);
Query OK, 1 row affected (1 min 17.58 sec)
7)因为mysql默认是
mysql> select @@concurrent_insert;
+---------------------+
| @@concurrent_insert |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
即等term1终端把t2表进行读锁后lock table t2 read local后，term2线程可以进行表尾追加写操作，可以成功,但其他的写操作都处于等待状态.
8)term1上:
mysql> lock table t2 read local;
Query OK, 0 rows affected (0.00 sec)
term2上:
mysql> select * from t2;
+------+
| id |
+------+
| 2 |
| 3 |
| 5 |
| 6 |
+------+
5 rows in set (0.00 sec)
mysql> insert into t2 values(7);
Query OK, 1 row affected (0.00 sec)
mysql> update t2 set id=7 where id=6;
等待.....
其次测试写锁:
1)term1:
mysql> lock table t2 write;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t2 values(8);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
| 7 |
| 7 |
| 8 |
+------+
7 rows in set (0.00 sec)
2)term2:
mysql> select * from t2;
等待....
mysql> insert into t2 values(9);
等待....
4.myisam存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0,1或2.
1)当concurrent_insert设置为0时,不允许并发插入.
2)当concurrent_insert设置为1时,如果myisam表中没有空洞(即表的中间没有被删除的行),myisam允许在一个进程读表的同时，另一个进程从表尾插入记录，这也是mysql的默认设置,注:表空洞可以用optimize table t2来优化,来减少碎片形成的空洞.
3)当concurrent_insert设置为2时，无论myisam表中有没有空洞,都允许表尾并发插入记录.。