MySQL锁及其机制的探究和详解

MySQL 锁

1、screen

在介绍MySQL锁之前啊,先给大家介绍一个工具的使用screen,便于大家实践检验真理:
Screen是一款由GNU计划开发的用于命令行终端切换的自由软件。用户可以通过该软件同时连接多个本地或远程的命令行会话,并在其间自由切换。GNU Screen可以看作是窗口管理器的命令行界面版本。它提供了统一的管理多个会话的界面和相应的功能。

我们这次用它的一个分屏多窗口的功能: GNU's Screen 官方站点 http://www.gnu.org/software/screen/

1.水平分割

命令: 1.1. screen -S d1 1.2 ctrl+a S, ctrl+a Tab, ctrl+a c

2.垂直分割

ctrl+a |

2.屏幕切换

CTRL+a,tab

2.新增窗口添加输入命令状态

CTRL+a,c

file

更多命令:http://man.linuxde.net/screen

2、MySQL锁概述

数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。
MySQL数据库由于其自身架构的特点,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎(已经被InnoDB取代)采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁

MySQL这3种锁的特性可大致归纳如下:

锁类型 开销、加锁速度 死锁 粒度 并发性能
表级锁 开销小,加锁快 不会出现死锁 锁定粒度大 发生锁冲突的概率最高,并发度最低
行级锁 开销大,加锁慢 会出现死锁 锁定粒度最小 发生锁冲突的概率最低,并发度最低
页面锁 开销和加锁时间界于表锁和行锁之间 会出现死锁 锁定粒度界于表锁和行锁之间 并发度一般。

分析:

  • 表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用;
  • 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用(行级锁定也最容易发生死锁哦)

测试表创建:

// myisam 测试表
CREATE TABLE `test_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET sjis DEFAULT NULL,
  `test` varchar(100) CHARACTER SET sjis DEFAULT NULL,
  `phone` varchar(11) CHARACTER SET sjis DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 

// innodb 测试表
CREATE TABLE `test_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET sjis DEFAULT NULL,
  `test` varchar(100) CHARACTER SET sjis DEFAULT NULL,
  `phone` varchar(11) CHARACTER SET sjis DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3、MyISAM表锁

MyISAM存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高,MySQL才开始开发基于事务的存储引擎,后来慢慢出现了支持行锁的InnoDB存储引擎(实际InnoDB是单独的一个公司,现在已经被Oracle公司收购)。

3.1 MySQL表级锁的锁模式

MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock) 对MyISAM表的读操作不会阻塞其他用户对同一表的请求,但会阻塞对同一表的请求; 对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作; MyISAM表的读操作与写操作之间、以及写操作之间是串行的。当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。

图3.0 MyISAM存储引擎的写阻塞读例子
session_1 session_2
获得表`test_myisam`的WRITE锁定:
mysql> lock table test_myisam write                                                       
Query OK, 0 rows affected (0.00 sec)                                                      
当前session对锁定表的查询、更新、插入操作都可以执行:
mysql> select * from test_myisam;
+----+------+-------+-------------+
| id | name | test  | phone       |
+----+------+-------+-------------+
|  1 | sqc  | tiner | 13717674039 |
+----+------+-------+-------------+
1 row in set (0.00 sec)

mysql> insert into test_myisam(name,test,
phone)values('qr','mylove','13975654');

Query OK, 1 row affected (0.00 sec)
其他session对锁定表的查询被阻塞,需要等待锁被释放:
mysql> select * from test_myisam;

等待!
释放锁:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)                                                    
等待
释放锁完成
mysql> select * from test_myisam;                                                         
+----+------+-------+-------------+                                                       
|  1 | sqc  | tiner | 13717674039 |                                                       
|  2 | qr   | mylove | 13975654    |
+----+------+-------+-------------+                                                       
2 rows in set (2 min 29.96 sec)

说明: 由上面的图3.0可以看出当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新、查询、插入等操作。其他线程的读、写操作都会等待,直到锁被释放为止。

图3.1 MyISAM存储引擎的读阻塞写例子
session_1 session_2
获得表`test_myisam`的`READ`锁定:
mysql> lock table test_myisam read;                                                       
Query OK, 0 rows affected (0.00 sec)                                                     
当前session可以查询该表记录
mysql> select * from test_myisam;
+----+------+--------+-------------+
| id | name | test   | phone       |
+----+------+--------+-------------+
|  1 | sqc  | tiner  | 13717674039 |
|  2 | qr   | mylove | 13975654    |
+----+------+--------+-------------+
2 rows in set (0.00 sec)

其他session也可以查询该表的记录:
mysql> select * from test_myisam;
+----+------+--------+-------------+
| id | name | test   | phone       |
+----+------+--------+-------------+
|  1 | sqc  | tiner  | 13717674039 |
|  2 | qr   | mylove | 13975654    |
+----+------+--------+-------------+
2 rows in set (0.00 sec)
当前session不能查询没有锁定的表:
mysql> select * from test_innodb;
ERROR 1100 (HY000): Table 'test_innodb' 
was not locked with LOCK TABLES
其他session可以查询或者更新、新增未锁定的表
mysql> select * from test_innodb;
+----+------+-------+-------------+
| id | name | test  | phone       |
 +----+------+-------+-------------+
|  1 | sqc  | xiaot | 13717674032 |
+----+------+-------+-------------+
1 row in set (0.00 sec)

mysql> update test_innodb set name='sqchr' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into test_innodb(name,test,phone)
values('jl','is a doubi','55555'); 
Query OK, 1 row affected (0.01 sec)

当前session中插入或者更新锁定的表都会提示错误:
mysql> insert into test_myisam(name,test,phone) 
values('syf','sister','13717674021');
ERROR 1099 (HY000): Table 'test_myisam' 
was locked with a READ lock and can't be updated

mysql> update test_myisam set name = 'sqchr' where id =1;
ERROR 1099 (HY000): Table 'test_myisam' 
was locked with a READ lock and can't be updated
其他session更新锁定表会等待获得锁:
mysql> update test_myisam set name = 'sqchr' where id =1;

等待...
释放锁:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
Session获得锁,更新操作完成:
mysql> update test_myisam set name = 'sqchr' where id =1;
Query OK, 1 row affected (1 min 0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

相关表锁的说明: MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,(因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本书的示例中,显式加锁基本上都是为了方便演示而已,并非必须如此。)
给MyISAM表显示加锁,一般是为了在一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。例如,有一个订单表orders,其中记录有各订单的总金额total,同时还有一个订单明细表order_detail,其中记录有各订单每一产品的金额小计
subtotal,假设我们需要检查这两个表的金额合计是否相符,可能就需要执行如下两条SQL:

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

这时,如果不先给两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,order_detail表可能已经发生了改变。因此,正确的方法应该是

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

注意: 当使用LOCK TABLES时,不仅需要一次锁定用到的所有表,而且,同一个表在SQL语句中出现多少次,就要通过与SQL语句中相同的别名锁定多少次,否则也会出错!举例说明如下。

(1)对actor表获得读锁:

mysql> lock table actor read;
Query OK, 0 rows affected (0.00 sec)

(2)但是通过别名访问会提示错误:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES

(3)需要对别名分别锁定:

mysql> lock table actor as a read,actor as b read;
Query OK, 0 rows affected (0.00 sec)

(4)按照别名的查询可以正确执行:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
+------------+-----------+------------+-----------+
| first_name | last_name | first_name | last_name |
+------------+-----------+------------+-----------+
| Lisa       | Tom       | LISA       | MONROE    |
+------------+-----------+------------+-----------+
1 row in set (0.00 sec)

要特别说明以下两点内容。 1、 如果上面的例子在LOCK
TABLES时加了“local”选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾并发插入记录,有关MyISAM表的并发插入问题,在后面的还会进一步介绍。
2、 在用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及到表的锁,并且MySQL不支持锁升级。也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。

3.2 查询表级锁争用情况

可以通过检查table_locks_waitedtable_locks_immediate状态变量来分析系统上的表锁定争夺:

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 99    |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.03 sec)

这里有两个状态变量记录MySQL内部表级锁定的情况,两个变量说明如下: Table_locks_immediate:产生表级锁定的次数; Table_locks_waited:出现表级锁定争用而发生等待的次数; 两个状态值都是从系统启动后开始记录,出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高,那么说明系统中表级锁定争用现象比较严重,就需要进一步分析为什么会有较多的锁定资源争用了

3.2.1 并发插入(Concurrent Inserts)

上文提到过MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert专门用以控制其并发插入的行为,其值分别可以为0、1或2。
1、当concurrent_insert设置为0时,不允许并发插入。
2、当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
3、当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

如session_1获得了一个表的READ LOCAL锁,该线程可以对表进行查询操作,但不能对表进行更新操作;其他的线程(session_2),虽然不能对表进行删除和更新操作,但却可以对该表进行并发插入操作,这里假设该表中间不存在空洞。

可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。例如,将concurrent_insert系统变量设为2,总是允许并发插入;同时,通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片,收回因删除记录而产生的中间空洞。

3.2.2 MyISAM的锁调度

前面讲过,MyISAM存储引擎的读锁和写锁是互斥的读写操作是串行的。那么,一个进程请求某个 MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!不过我们可以通过一些设置来调节MyISAM 的调度行为。

1、通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
2、通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
3、通过指定INSERT、UPDATE、DELETE语句LOW_PRIORITY属性,降低该语句的优先级。
虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。
另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。 上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

4、InnoDB锁问题

InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。行级锁与表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题。下面我们先介绍一点背景知识,然后详细讨论InnoDB的锁问题。

4.1、背景知识

4.1.1、事务(Transaction)及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
1、原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
2、一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
3、隔离性(Isolation):隔离性还有其他的称呼,如并发控制、可串行化、锁。事务的隔离性保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,通常这使用锁来实现。
4、持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。因此持久性保证事务系统的高可靠性(High Reliability),而不是高可用性(High Available)。对于高可用性的实现,事务本事并不能提供,需要一些系统的共同配合来完成。

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

4.1.2、并发事务处理

事务隔离的实现方式:

1、悲观锁:在读取数据前,对其加锁,阻止其他事务对数据进行修改

2、MVCC(多版本并发控制): 不加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度来看,好像是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制(MultiVersion Concurrency Control,简称MVCC或MCC)。

并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多的用户。当然它也有缺点,下面咱们来说说并发事务处理带来的问题;

4.1.3、并发事务处理带来的问题

相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况。
1、更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题--最后的更新覆盖了由其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题。
情境描述:
有a,b两个事务并发:

num=100  
a update set num+=1 num=101  
b update set num+=2 num=102  
a先提交 b再提交,a的更新丢失  

2、脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
情境描述:
有a,b两个事务并发:

a begin transaction  
a insert 事务未提交,  
b select (可以看到a的insert),   
a rollback  
b select (看不到a的insert)  
从头到尾b蒙圈儿了,他就read了两次,结果却不同,此为脏读。

3、不可重复读(Non-Repeatable Reads):一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。
情境描述:
有a,b两个事务并发:

a begin transaction  
a select (第一次查询的结果)  
b begin transaction  
b update|delete  
b commit  
a select (第二次查询的结果,与第一次不相同,不重复,简称不可重复读)  
a commit   
a在一个事物里读两次数据不同,造成a里的其他事物操作产生偏差,(能控制b编辑完a在 整体操作就好了)  

4、幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”,Phantom Read 指的是插入新行,update|delete不归为幻读 情境描述:
有a,b两个事务并发:

a begin transaction  
a select where ID>100 ,(假如只有101一条数据)  
b insert ID=102,(这里是insert表示幻读,要是为update,就成不可重复读了)  
b commit   
a select where ID>100 (有101,102两条数据)(b no commit能读出来叫脏读;b commit 叫不可重复读)  
a begin transaction  
a查询了两次,返回的数据行竟然不相等  
4.1.4、事务隔离级别

在上面讲到的并发事务处理带来的问题中,“更新丢失”通常是应该完全避免的。但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。
数据库实现事务隔离的方式,基本上可分为以下两种(上面已经讲过,这里重新列出加深印象)。
1、一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。
2、另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度来看,好像是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制(MultiVersion Concurrency Control,简称MVCC或MCC),也经常称为多版本数据库。
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上 “串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾,ISO/ANSI SQL92定义了4个事务隔离级别,每个级别的隔离程度不同,允许出现的副作用也不同,应用可以根据自己的业务逻辑要求,通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。下表很好地概括了这4个隔离级别的特性。

隔离级别 \ 读数据一致性及允许的并发副作用 读数据一致性 脏读 不可重复读 幻读 加锁读
未提交读(Read uncommitted) 最低级别,只能保证不读取物理上损坏的数据
已提交度(Read committed) 语句级
可重复读(Repeatable read) 事务级
可序列化(Serializable) 最高级别,事务级

最后要说明的是:各具体数据库并不一定完全实现了上述4个隔离级别,例如,Oracle只提供Read committed和Serializable两个标准隔离级别,另外还提供自己定义的Read only隔离级别;SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外,还支持一个叫做“快照”的隔离级别,但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别,但在具体实现时,有一些特点,比如在一些隔离级别下是采用MVCC一致性读,但某些情况下又不是,这些内容请读者自行去了解。不错,因为我懒得整理。

相关命令

1.查看当前会话隔离级别  
select @@tx_isolation;  
2.查看系统当前隔离级别  
select @@global.tx_isolation;  
3.设置当前会话隔离级别  
set session transaction isolatin level repeatable read;  
4.设置系统当前隔离级别  
set global transaction isolation level repeatable read;  
5.命令行,开始事务时  
set autocommit=off 或者 start transaction 

相关文档:

http://xm-king.iteye.com/blog/770721
http://www.jb51.net/article/100183.htm
http://blog.csdn.net/ty_hf/article/details/77624098
http://blog.csdn.net/whoamiyang/article/details/51901888

4.2、获取InnoDB行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

mysql> show status like 'innodb_row_lock%'; 
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

如果发现锁争用比较严重,如InnoDB_row_lock_waitsInnoDB_row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。

4.3、InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁
1、共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
2、排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁
3、意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
4、意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
上述锁模式的兼容情况具体如下表

请求锁模式
是否兼容
当前锁模式
X IX S IS
X 不兼容 不兼容 不兼容 不兼容
IX 不兼容 兼容 不兼容 兼容
S 不兼容 不兼容 兼容 兼容
IS 不兼容 兼容 兼容 兼容

如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。

意向锁是InnoDB自动加的,不需用户干预。
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显性地给记录集加共享锁或排他锁。
1、共享锁(S):SELECT FROM table_name WHERE ... LOCK IN SHARE MODE。
2、排他锁(X):SELECT
FROM table_name WHERE ... FOR UPDATE。
注意:用SELECT ... IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT... FOR UPDATE方式获得排他锁

4.3.1、InnoDB存储引擎的共享锁例子

使用了SELECT ... IN SHARE MODE加锁后再更新记录,看看会出现什么情况,其中test_innodb表的id字段为主键。

图4.1 InnoDB存储引擎的共享锁例子
session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+ 
| id | name  | test  | phone       |
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)  
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+ 
| id | name  | test  | phone       |
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)  
当前session对id=1的记录加share mode 的共享锁:
mysql> select  * from test_innodb where id =1 lock in share mode; 
+----+-------+-------+-------------+
| id | name  | test  | phone       |
+----+-------+-------+-------------+
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)  
其他session也可以查询该表的记录:
并也可以对该记录加share mode的共享锁:
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+
| id | name  | test  | phone       |
+----+-------+-------+-------------+
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)
mysql> select  * from test_innodb where id =1 lock in share mode;
+----+-------+-------+-------------+
| id | name  | test  | phone       |
+----+-------+-------+-------------+
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.01 sec)
当前session对锁定的记录进行更新操作,等待锁:
mysql> update test_innodb set name ='wdsqc' where id =1;
等待中...
然后session2也对该记录进行更新操作,则会导致死锁退出:
mysql> update test_innodb set name ='wdsqc' where id =1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
获得锁后,可以成功更新:
mysql>  update test_innodb set name ='wdsqc' where id =1;
Rows matched: 1  Changed: 1  Warnings: 0
提交commit;
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+
| id | name  | test  | phone       |
+----+-------+-------+-------------+
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+
| id | name  | test  | phone       |
+----+-------+-------+-------------+
|  1 | wdsqc | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)
查看当前会话的隔离级别,不存在脏读和不可重复读,所以需要提交session1提交后&& session2提交后方可看到数据的改动(sqchr=>wdsqc)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
4.3.2、InnoDB存储引擎的排他锁例子

使用了SELECT ... for update;,其中test_innodb表的id字段为主键。

图4.1 InnoDB存储引擎的排他锁例子
session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+ 
| id | name  | test  | phone       |
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)  
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+ 
| id | name  | test  | phone       |
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)  
当前session对id=1的记录加for update的排它锁:
mysql> select  * from test_innodb where id =1 for update; 
+----+-------+-------+-------------+
| id | name  | test  | phone       |
+----+-------+-------+-------------+
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)  
其他session也可以查询该表的记录:
但是不能对该记录加共享锁,会等待获得锁:
mysql> select  * from test_innodb where id =1;
+----+-------+-------+-------------+
| id | name  | test  | phone       |
+----+-------+-------+-------------+
|  1 | sqchr | xiaot | 13717674032 |
+----+-------+-------+-------------+
1 row in set (0.00 sec)

mysql> select  * from test_innodb where id =1 for update;

等待。。。
当前session可以对锁定的记录进行更新操作,更新后释放锁:
mysql>  update test_innodb set name ='wdsqc11111' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session2获得锁 commit后(是否commit后得到和隔离级别有关),得到session1提交的记录:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from test_innodb where id =1;
+----+------------+-------+-------------+
| id | name       | test  | phone       |
+----+------------+-------+-------------+
|  1 | wdsqc11111 | xiaot | 13717674032 |
+----+------------+-------+-------------+
1 row in set (0.00 sec)

4.4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面通过一些实际例子来加以说明。

(1)在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。
(2)由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
(4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。
因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。 在下面的例子中,检索值的数据类型与索引字段不同,虽然MySQL能够进行数据类型转换,但却不会使用索引,从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划,我们可以清楚地看到了这一点。

例子中tab_with_index表的name字段有索引,但是name字段是varchar类型的,如果where条件中不是和varchar类型进行比较,则会对name进行类型转换,而执行的全表扫描。

mysql> alter table tab_no_index add index name(name);
Query OK, 4 rows affected (8.06 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> explain select * from tab_with_index where name = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_with_index
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from tab_with_index where name = '1' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_with_index
         type: ref
possible_keys: name
          key: name
      key_len: 23
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

相关文章: http://blog.csdn.net/xifeijian/article/details/20313977

附:能看到这里你已经很有耐心了。说明你是个有前途的人,哈哈。当然下面还有间隙锁和、InnoDB在不同隔离级别下的一致性读及锁的差异及死锁等的一些知识点的整理。另开文章进行讲述。稍后会附上链接~