mysql的存储引擎与锁

一、背景知识

1、锁是计算机协调多个进程或线程并发访问某一资源的机制。

A、锁分类。

| 共享锁（读锁）：在锁定期间，多个用户可以读取同一个资源，读取过程中数据不会发生变化。

| 排他锁（写锁）：在锁定期间，只允许一个用户写入数据，其它用户的读取，写入等操作都会被拒绝。

B、锁颗粒

| 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

| 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

| 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

2、事务是由一组SQL语句组成的逻辑处理单元。

A、事务（Transaction）及其ACID属性。

| 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

|   一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

| 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

| 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

银行转帐就是事务的一个典型例子。

B、事务并发问题

| 更新丢失（Lost Update）：两个或多个事务同时更新一个资源，前面的事务的操作结果会被最后面的事务的操作结果覆盖。

| 脏读（Dirty Reads）：事务A正在更新记录X，事务B读取了记录X，并藉此做进一步处理，事务A更新完毕记录X，此时数据库中的记录X与事务B读到的记录X并不是一致的。那么事务B读取到的就是“脏数据”，此类现象称之为“脏读”。

| 不可重复读（Non-Repeatable Reads）：事务A读取了记录X，然后事务B更新了记录X，然后事务A再次读取记录X。此类情况下，事务A读取的记录X并不一定是相同的。

| 幻读（Phantom Reads）：事务Ａ按相同的查询条件读取已经读取过的记录Ｘ时，发现，事务Ｂ更新的记录Ｙ也符合事务Ａ的查询条件。那么，事务A将会读取到记录Ｙ，而不是记录Ｘ。此类现象称之为“幻读”。

Ｃ、事务隔离解决事务并发问题

隔离级别 说明 脏读 不可重复读 幻读
Read Uncommitted（读取未提交内容） 所有事务都可以看到其他未提交事务的执行结果。 是 是 是
Read Committed（读取提交内容） 一个事务只能看见已经提交事务所做的改变。 否 是 是
Repeatable Read（可重读） 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。 否 否 是
Serializable（可串行化） 它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。 否 否 否

二、各个存储引擎的特点

特点 MyISAM InnoDB Memory Archive
存储限制 256TB 64TB 有 无
事务安全 - 支持 - -
支持索引 支持 支持 支持  
锁颗粒 表锁 行锁 表锁 行锁
数据压缩 支持 - - 支持
支持外键 - 支持 - -

三、MyISAM的锁详解

在我的test数据库中，有两张MyISAM存储引擎的表分别是User与Log。

下面将演示两个线程（A、B）同时操作一张表的情况，开两个ｃｍｄ窗口一个代表Ａ一个代表Ｂ按顺序执行下面的代码。

操作 说明 线程
mysql> show tables;
+----------------+
| Tables_in_test   |
+----------------+
| log                   |
| user                 |
+----------------+
2 rows in set		 显示所有的表 A
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name            | Value   |
+-----------------------+-------+
| Table_locks_immediate | 50      |
| Table_locks_waited      | 0        |
+-----------------------+-------+
2 rows in set (0.00 sec)		 显示表级锁争用情况 A
mysql> lock table log write local;
Query OK, 0 rows affected		 给表log显式加上写锁，“local”选项的作用是在满足MyISAM表并发插入条件的情况下，允许其它线程在表尾并发插入记录。 A
mysql> select * from log;

 线程Ｂ读取已经被线程Ａ上了读锁的表ｌｏｇ，可以看到线程Ｂ正在等待。 Ｂ
mysql> select * from user;
1100 - Table 'user' was not locked with LOCK TABLES		 线程Ａ读取了没有被加锁的表Ｕｓｅｒ，可以看到，ｍｙｓｑｌ不让读取。 Ａ
mysql> unlock tables;
Query OK, 0 rows affected

 线程A释放了对log表的锁。 A
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name            | Value   |
+-----------------------+-------+
| Table_locks_immediate | 51      |
| Table_locks_waited      | 0        |
+-----------------------+-------+
2 rows in set (0.00 sec)		 显示表级锁争用情况，可以看到Table_locks_immediate的值加1了。 A
+--------+-----+---------+-------------+
| log_id | uid | content | create_time |
+--------+-----+---------+-------------+
|      1 |   2       | 呵呵       |          10      |
|      2 |   2       | 哈哈       |          20      |
+--------+-----+---------+-------------+
2 rows in set (5 min 47.34 sec)		 线程B读取到了log的数据。 B

1、总结

| MyISAM，在默认情况下会自动加锁，并不需要显式加锁。
| MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。其它未加锁的表，并不允许操作。
| MyISAM加的是表级锁。
| 当线程Ａ与线程Ｂ同时要给某个表上读锁与写锁的时候，MyISAM默认让线程Ｂ先上写锁。大量的读与写操作并存的时候，写操作可能会一直得到写锁，导致读操作处于阻塞状态。

2、并发插入调度

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
| 当concurrent_insert设置为0时，不允许并发插入。
| 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
| 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

3、MyISAM的锁调度

| 通过指定启动参数 low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
| 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
| 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
| 给系统变量max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读操作一定获得锁的机会。

 四、InnoDB的锁详解

在我的test数据库中，有两张InnoDB存储引擎的表分别是User与Log。

下面将演示两个线程（A、B）同时操作一张表的情况，开两个ｃｍｄ窗口一个代表Ａ一个代表Ｂ按顺序执行下面的代码。

第一个表格是，排他锁的实例。

操作 说明 线程
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| log                 |
| user               |
+----------------+
2 rows in set		 显示所有的表 A
mysql> select * from log;
+--------+-----+---------+-------------+
| log_id | uid | content | create_time |
+--------+-----+---------+-------------+
|      1 |   1 | 呵呵         |           0     |
|      2 |   2 | 哈哈         |           0     |
+--------+-----+---------+-------------+
2 rows in set		 显示log表的信息 A
mysql> show status like 'innodb_row_lock%';
+-------------------------------+----------+
| Variable_name                       | Value       |
+-------------------------------+----------+
| Innodb_row_lock_current_waits | 0          |
| Innodb_row_lock_time              | 185770 |
| Innodb_row_lock_time_avg       | 26538   |
| Innodb_row_lock_time_max      | 51620   |
| Innodb_row_lock_waits             | 7          |
+-------------------------------+----------+
5 rows in set (0.00 sec)		 查看InnoDB的锁争用情况  B
mysql> start transaction;
Query OK, 0 rows affected		 开启事务 A
mysql> update log set create_time=1 where uid=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

更新uid=1的数据

Select…For Update 语句可以在读取数据的时候就锁定它。

 A
mysql> update log set create_time=2 where uid=2;

更新uid=2的数据，可以看线程B在等待，因为此时InnoDB加的是表级排他锁。

 B
mysql> commit;
Query OK, 0 rows affected		 提交事务 A
mysql> update log set create_time=2 where uid=2;
Query OK, 1 row affected (9.69 sec)
Rows matched: 1  Changed: 1  Warnings: 0		 线程B执行update成功 B
mysql> show status like 'innodb_row_lock%';
+--------------------------------+---------+
| Variable_name                         | Value     |
+--------------------------------+---------+
| Innodb_row_lock_current_waits | 0          |
| Innodb_row_lock_time              | 196980 |
| Innodb_row_lock_time_avg       | 24622   |
| Innodb_row_lock_time_max      | 51620   |
| Innodb_row_lock_waits             | 8          |
+--------------------------------+---------+
5 rows in set (0.00 sec)

再次查看InnoDB的锁争用情况 。

如　InnoDB_row_lock_waits　和InnoDB_row_lock_time_avg　的值比较高，则锁的争用情况严重。

 B
mysql> select * from log;
+--------+-----+---------+-------------+
| log_id | uid | content | create_time |
+--------+-----+---------+-------------+
|      1 |   1 | 呵呵          |           1    |
|      2 |   2 | 哈哈          |           2    |
+--------+-----+---------+-------------+
2 rows in set (0.00 sec)		 显示log表的信息 B

 第二个表格是，共享锁的实例。

操作 说明 线程
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)		 开启事务 A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)		 开启事务 B
mysql> select * from log where uid=1 lock in share mode;
+--------+-----+---------+-------------+
| log_id | uid | content | create_time |
+--------+-----+---------+-------------+
|      1 |   1 | 呵呵    |           1 |
+--------+-----+---------+-------------+
1 row in set (0.00 sec)		 线程Ａ取得共享锁 Ａ
mysql> select * from log where uid=1 lock in share mode;
+--------+-----+---------+-------------+
| log_id | uid | content | create_time |
+--------+-----+---------+-------------+
|      1 |   1 | 呵呵    |           1 |
+--------+-----+---------+-------------+
1 row in set (0.00 sec)		 线程Ｂ取得共享锁 Ｂ
mysql> update log set create_time=1 where uid=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

线程Ａ更新一条记录，暂时不要去操作下面的步骤，直到出现报错。

由于线程Ｂ的共享锁，导致了线程A无法更新数据。

 A
mysql> update log set create_time=1 where uid=1;
Query OK, 0 rows affected (7.82 sec)
Rows matched: 1  Changed: 0  Warnings: 0		 线程Ａ再次更新记录，同时在线程A还在等待的时候，线程B也执行了更新记录。 A
mysql> update log set create_time=1 where uid=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction		 线程B执行更新记录。注意，当线程B执行更新语句的时候，线程B失去了共享锁，线程A获得了排他锁，导致线程B的语句立刻出现了报错。 B
mysql> select * from log where uid=1 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

线程Ｂ再次执行获取共享锁的语句，可以看到，由于线程Ａ已经取得了排他锁，导致线程Ｂ争锁失败。

如果线程Ａ执行ｃｏｍｍｉｔ语句释放排他锁后，线程Ｂ则可以立马获得共享锁。

 B

总结：

InnoDB行锁是通过给索引上的索引项加锁来实现的，无索引的情况下，InnoDB将使用表锁！实际应用中，要特别注意此特性。避免大量锁冲突影响并发性能。

posted @ 2015-10-03 16:02  扬空  阅读(281)  评论(0)  编辑  收藏  举报