mysql的锁机制-初步

总结一下mysql的锁机制，主要参考资料：手册lock_table
主要使用：
先在test库中创建测试表test1

mysql> use test;
mysql> create table `test1` ( `id` int(10) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;//并在test1表中插入1-7共7行数据以供测试

先讲读锁（read lock)

mysql>lock tables test1 read; //同时给所有数据库加读锁FLUSH TABLES WITH READ LOCK
mysql> insert into test1 values(8);
ERROR 1099 (HY000): Table 'test1' was locked with a READ lock and can't be updated

与此同时，另外的一个连接

mysql> select * from test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)
//可以读，但不能写
mysql> insert into test1 values(8);
Query OK, 1 row affected (1 min 59.94 sec)//两分钟是上面进程锁表的时间

关于解锁

mysql> unlock tables ; //解除当前连接建立的所有的锁

再讲写锁

mysql> lock tables test1 write;
Query OK, 0 rows affected (0.00 sec)
//本连接，锁表的连接
mysql> select * from test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.00 sec)
//可以读
mysql> insert test1 value(9);
Query OK, 1 row affected (0.00 sec)
//可以写

同时其他连接

mysql> select * from test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+----+
9 rows in set (27.84 sec)
//同时不能读
mysql> insert test1 value(10);
Query OK, 1 row affected (35.55 sec)
//也不能写