Mysql中那些锁机制之MyISAM_MySQL

说到锁机制之前，先来看看Mysql的存储引擎，毕竟不同的引擎的锁机制也随着不同。

三类常见引擎：

 

MyIsam ：不支持事务，不支持外键，所以访问速度快。锁机制是表锁，支持全文索引

 

InnoDB ：支持事务、支持外键，所以对比MyISAM，InnoDB的处理效率差一些，并要占更多的磁盘空间保留数据和索引。锁机制是行锁，不支持全文索引

 

Memory：数据是存放在内存中的，默认哈希索引，非常适合存储临时数据，服务器关闭后，数据会丢失掉。

 

如何选择存储引擎：

 

 

MyISAM：应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。

 

InnoDB：用于事务处理应用程序，支持外键，如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性。更新删除等频繁（InnoDB可以有效的降低由于删除和更新导致的锁定），对于数据准确性要求比较高的，此引擎适合。

 

Memory：通常用于更新不太频繁的小表，用以快速得到访问结果。

 

Mysql中的锁

 

 

如果熟悉多线程，那么对锁肯定是有概念的，锁是计算机协调多个进程或线程对某一资源并发访问的机制。

 

Mysql中的锁分为表锁和行锁：

顾名思义，表锁就是锁住一张表，而行锁就是锁住一行。

 

表锁的特点：开销小，不会产生死锁，发生锁冲突的概率高，并且并发度低。

行锁的特点：开销大，会产生死锁，发生锁冲突的概率低，并发度高。

 

因此MyISAM和Memory引擎采用的是表锁，而InnoDB存储引擎采用的是行锁。

MyISAM的锁机制：

分为共享读锁和独占写锁。

读锁是：当某一进程对某张表进行读操作时（select），其他线程也可以读，但是不能写。简单的理解就是，我读的时候你不能写。

写锁是：当某一进程对某种表某张表的写时（insert，update，，delete），其他线程不能写也不能读。可以理解为，我写的时候，你不能读，也不能写。

 

因此MyISAM的读操作和写操作，以及写操作之间是串行的！MyISAM在执行读写操作的时候会自动给表加相应的锁（也就是说不用显示的使用lock table命令），MyISAM总是一次获得SQL语句所需要的全部锁，这也是MyISAM不会出现死锁的原因。

 

 

下面分别举关于写锁和读锁的例子：

 

写锁：

 

事务1	事务2
取得first_test表的写锁： mysql> lock table first_test write; Query OK, 0 rows affected (0.00 sec)
当前事务对查询、更新和插入操作都可以执行 mysql> select * from first_test ; +----+------+ | id | age | +----+------+ | 1 | 10 | | 2 | 11 | | 3 | 12 | | 4 | 13 | +----+------+ 4 rows in set (0.00 sec) mysql> insert into first_test(age) values(14); Query OK, 1 row affected (0.11 sec)	其他事务对锁定表的查询被阻塞，需要等到锁被释放，才可以执行 mysql> select * from first_test; 等待......
mysql> unlock table; Query OK, 0 rows affected (0.00 sec)	等待
	mysql> select * from first_test; +----+------+ | id | age | +----+------+ | 1 | 10 | | 2 | 11 | | 3 | 12 | | 4 | 13 | | 5 | 14 | +----+------+ 5 rows in set (9 min 45.02 sec)

 

 

读锁例子如下：

 

事务1	事务2
获得表first_read的锁定 mysql> lock table first_test read; Query OK, 0 rows affected (0.00 sec)
当前事务可以查询该表记录： mysql> select * from first_test; +----+------+ | id | age | +----+------+ | 1 | 10 | | 2 | 11 | | 3 | 12 | | 4 | 13 | | 5 | 14 | +----+------+ 5 rows in set (0.00 sec)	其他事务也可以查到该表信息 mysql> select * from first_test; +----+------+ | id | age | +----+------+ | 1 | 10 | | 2 | 11 | | 3 | 12 | | 4 | 13 | | 5 | 14 | +----+------+ 5 rows in set (0.00 sec)
但是当前事务不能查询没有锁定的表： mysql> select * from goods; ERROR 1100 (HY000): Table 'goods' was not locked with LOCK TABLES	其他事务可以查询或更新未锁定的表： mysql> select * from goods; +----+------------+------+ | id | name | num | +----+------------+------+ | 1 | firstGoods | 11 | | 3 | ThirdGoods | 11 | | 4 | fourth | 11 | +----+------------+------+ 10 rows in set (0.00 sec)
而且插入更新锁定的表都会报错： mysql> insert into first_test(age) values(15); ERROR 1099 (HY000): Table 'first_test' was locked with a READ lock and can't be updated   mysql> update first_test set age=100 where id =1; ERROR 1099 (HY000): Table 'first_test' was locked with a READ lock and can't be updated	当更新被锁定的表时会等待： mysql> update first_test set age=100 where id =1; 等待......
mysql> unlock table; Query OK, 0 rows affected (0.00 sec)	mysql> update first_test set age=100 where id =1; Query OK, 1 row affected (38.82 sec) Rows matched: 1 Changed: 1 Warnings: 0

 

并发插入

刚说到Mysql在插入和修改的时候都是串行的，但是MyISAM也支持查询和插入的并发操作。

 

MyISAM中有一个系统变量concurrent_insert（默认为1），用以控制并发插入（用户在表尾插入数据）行为。

当concurrent_insert为0时，不允许并发插入。

当concurrent_insert为1时，如果表中没有空洞（中间没有被删除的行），MyISAM允许一个进程在读表的同时，另一个进程从表尾插入记录。

当concurrent_insert为2时，无论MyISAM表中有没有空洞，都可以在末尾插入记录

 

事务1	事务2
mysql> lock table first_test read local; Query OK, 0 rows affected (0.00 sec) --加入local选项是说明，在表满足并发插入的前提下，允许在末尾插入数据
当前进程不能进行插入和更新操作 mysql> insert into first_test(age) values(15); ERROR 1099 (HY000): Table 'first_test' was locked with a READ lock and can't be updated   mysql> update first_test set age=200 where id =1; ERROR 1099 (HY000): Table 'first_test' was locked with a READ lock and can't be updated	其他进程可以进行插入，但是更新会等待： mysql> insert into first_test(age) values(15); Query OK, 1 row affected (0.00 sec)   mysql> update first_test set age=200 where id =2; 等待.....
当前进程不能不能访问其他进程插入的数据   mysql> select * from first_test; +----+------+ | id | age | +----+------+ | 1 | 100 | | 2 | 11 | | 3 | 12 | | 4 | 13 | | 5 | 14 | | 6 | 14 | +----+------+ 6 rows in set (0.00 sec)
释放锁以后皆大欢喜 mysql> unlock table; Query OK, 0 rows affected (0.00 sec)	等待
插入的和更新的都出来的：   mysql> select * from first_test; +----+------+ | id | age | +----+------+ | 1 | 100 | | 2 | 200 | | 3 | 12 | | 4 | 13 | | 5 | 14 | | 6 | 14 | | 7 | 15 | +----+------+ 7 rows in set (0.00 sec)	mysql> update first_test set age=200 where id =2; Query OK, 1 row affected (1 min 39.75 sec) Rows matched: 1 Changed: 1 Warnings: 0

 

需要注意的：

并发插入是解决对同一表中的查询和插入的锁争用。

如果对有空洞的表进行并发插入会产生碎片，所以在空闲时可以利用optimize table命令回收因删除记录产生的空洞。

锁调度

 

在MyISAM中当一个进程请求某张表的读锁，而另一个进程同时也请求写锁，Mysql会先让后者获得写锁。即使读请求比写请求先到达锁等待队列，写锁也会插入到读锁之前。

因为Mysql总是认为写请求一般比读请求重要，这也就是MyISAM不太适合有大量的读写操作的应用的原因，因为大量的写请求会让查询操作很难获取到读锁，有可能永远阻塞。

 

处理办法：

1、指定Insert、update、delete语句的low_priority属性，降低其优先级。

2、指定启动参数low-priority-updates，使得MyISAM默认给读请求优先的权利。

3、执行命令set low_priority_updates=1，使该连接发出的请求降低。

 

4、指定max_write_lock_count设置一个合适的值，当写锁达到这个值后，暂时降低写请求的优先级，让读请求获取锁。

 

但是上面的处理办法造成的原因就是当遇到复杂的查询语句时，写请求可能很难获取到锁，这是一个很纠结的问题，所以我们一般避免使用复杂的查询语句，如果如法避免，则可以再数据库空闲阶段（深夜）执行。