文章参照整理:http://www.cnblogs.com/qq78292959/archive/2013/01/30/2883109.html
一、锁的概述
1.1、为什么要引入锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
- 丢失更新
两个用户读同一数据并进行修改,其中一个用户的修改结果破坏/覆盖了另一个修改的结果
- 脏读
用户A修改了数据,随后用户B又读出该数据,但用户A因为某些原因取消了对数据的修改,数据恢复原值。此时B得到的数据就与数据库内的数据产生了不一致
- 不可重复读
用户A读取数据,随后用户B读出该数据并修改,此时用户A再读取数据时发现前后两次的值不一致
1.2、锁的分类
锁的类别有两种分法:
1. 从程序员的角度看:分为乐观锁和悲观锁。
- 乐观锁:完全依靠数据库来管理锁的工作。
- 悲观锁:程序员自己管理数据或对象上的锁处理。
2. 从数据库系统的角度来看:分为独占锁,共享锁和更新锁等
相对其他数据库而言,MySQL 的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。
MySQL锁类型大致可归纳为以下3种锁:
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
二、Mysql 锁
2.1、MyISAM 引擎的锁模式
MyISAM 存储引擎只支持表锁,这也是 MySQL 开始几个版本中唯一支持的锁类型。随着应用对事务完整性和 并发性要求的不断提高,MySQL 才开始开发基于事务的存储引擎,后来慢慢出现了支持页锁的 BDB 存储引擎和支持行锁的 InnoDB 存储引擎。但是 MyISAM 的表锁依然是使用最为广泛的锁类型。
MySQL的表级锁有两种模式:表共享读锁和表独占写锁。
- 共享锁:共享锁锁定的资源可以被其他用户读取,但其他用户无法修改它
- 独占锁:只允许进行锁定操作的程序使用,其他任何对他的操作均不会被接受。
对 MyISAM 表的读操作(共享锁),不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;共享锁锁定的资源可以被其他用户读取,但其他用户无法修改它。对 MyISAM 表的写操作(独占锁),则会阻塞其他用户对同一表的读和写请求,直到锁被释放为止。MyISAM 表的读和写操作之间,以及写和写操作之间是串行的!
分析系统上的表锁定争夺可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来进行:
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。
2.2、如何给 MyISAM 引擎的表加锁
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。在本书的示例中,显式加锁基本上都是为了方便而已,并非必须如此。
给MyISAM表显式加锁,一般是为了在一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。例如:
例1:有一个订单表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;
说明:
1)上面的例子在 LOCK TABLES 时加了 “local” 选项,其作用就是在满足 MyISAM 表并发插入条件的情况下,允许其他用户在表尾并发插入记录,有关 MyISAM 表的并发插入问题,在后面的章节中介绍。
2)在用 LOCK TABLES 给表显式加表锁时,必须同时取得所有涉及到表的锁,并且 MySQL 不支持锁升级。也就是说,在执行 LOCK TABLES 后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。
3)当使用 LOCK TABLES 时,不仅需要一次锁定用到的所有表,而且,同一个表在 SQL 语句中出现多少次,就要通过与 SQL 语句中相同的别名锁定多少次(就是说把表的别名锁进去),否则也会出错!
例2:对actor表获得读锁,但是通过别名访问会提示错误:
mysql> lock table actor read; Query OK, 0 rows affected (0.00 sec) 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
但是对别名分别锁定,再按照别名的查询可以正确执行:
mysql> lock table actor as a read,actor as b read; Query OK, 0 rows affected (0.00 sec) 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)
2.3、并发插入问题
前面说到 MyISAM 表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM 表也支持查询和插入操作的并发进行。
MyISAM 存储引擎有一个系统变量 concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
- 当 concurrent_insert 设置为 0 时,不允许并发插入。
- 当 concurrent_insert 设置为 1 时,如果 MyISAM 表中没有空洞(即表的中间没有被删除的行),MyISAM 允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是 MySQL 的默认设置。
- 当 concurrent_insert 设置为 2 时,无论 MyISAM 表中有没有空洞,都允许在表尾并发插入记录。
可以利用 MyISAM 存储引擎的并发插入特性,来解决应 用中对同一表查询和插入的锁争用。例如,将 concurrent_insert 系统变量设为 2,总是允许并发插入;同时,通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片,收回因删除记录而产生的中间空洞。有关 OPTIMIZE TABLE 语句的详细介绍,可以参见别的文章。
2.4、MyISAM的锁调度问题
前面讲过,MyISAM 存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个 MyISAM 表的读锁,同时另一个进程也请求同一表的写锁,MySQL 如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为 MySQL 认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!幸好我们可以通过一些设置来调节 MyISAM 的调度行为。
- 通过指定启动参数 low-priority-updates,使 MyISAM 引擎默认给予读请求以优先的权利。
- 通过执行命令 SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
- 通过指定 INSERT、UPDATE、DELETE 语句的 LOW_PRIORITY 属性,降低该语句的优先级。
虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。
三、常用的 InnoDB 引擎锁
InnoDB 与 MyISAM 的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。
2.1、事务(Transaction)及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元,事务具有4属性,通常称为事务的ACID属性。
- 原子性(Actomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
- 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
- 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
- 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
2.2、并发事务带来的问题
- 更新丢失(Lost Update):
- 脏读(Dirty Reads):
- 不可重复读(Non-Repeatable Reads):
- 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。
2.3、InnoDB的锁模式
InnoDB 实现了以下两种类型的行锁。
- 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
- 排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
-
意向共享锁(IS):事务打算给数据行加共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
-
意向排他锁(IX):事务打算给数据行加排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的,不需用户干预。对于 UPDATE、DELETE和INSERT 语句,InnoDB 会自动给涉及数据集加排他锁(X);对于普通 SELECT 语句,InnoDB 不会加任何锁。
2.4、给Innodb引擎数据库加锁
事务可以通过以下语句显式给记录集加共享锁或排他锁:
SELECT * FROM table_name WHERE ...LOCK IN SHARE MODE; # 加共享锁
SELECT * FROM table_name WHERE ... FOR UPDATE; # 加排它锁
用 SELECT ... IN SHARE MODE 获得共享锁,主要用在需要数据依存关系时确认某行记录是否存在,并确保没有人对这个记录进行UPDATE 或者 DELETE 操作。但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁。
注意:InnoDB 行锁是通过索引上的索引项来实现的,这一点 MySQL 与 Oracle 不同,后者是通过在数据中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味者:只有通过索引条件检索数据,InnoDB才会使用行级锁,否则,InnoDB将使用表锁!(锁升级)
2.5、Next-Key,间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时(一般我不会这么做),InnoDB会给符合条件的已有数据的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。举例来说,
例3. 假如 emp 表中只有101条记录,其 empid 的值分别是1,2,...,100,101。执行下面的SQL:
SELECT * FROM emp WHERE empid > 100 FOR UPDATE;
是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。
InnoDB 使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了 empid 大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另一方面,是为了满足其恢复和复制的需要。
有关其恢复和复制对机制的影响,以及不同隔离级别下InnoDB使用间隙锁的情况。
很显然,在使用范围条件检索并锁定记录时,InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。
2.6、InnoDB什么时候加表锁
对于 InnoDB 表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择 InnoDB 表的理由。但在个别特殊事务中,也可以考虑使用表级锁。
- 第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。
当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表。
在 InnoDB 下,使用表锁要注意以下两点:
1)使用 LOCK TALBES 虽然可以给 InnoDB 加表级锁,但必须说明的是,表锁不是由 InnoDB 存储引擎层管理的,而是由其上一层 MySQL Server 负责的,仅当 autocommit=0、innodb_table_lock=1(默认设置)时,InnoDB 层才能知道 MySQL 加的表锁,MySQL Server 才能感知 InnoDB 加的行锁,这种情况下,InnoDB 才能自动识别涉及表级锁的死锁。否则,InnoDB 将无法自动检测并处理这种死锁。
2)在用 LOCAK TABLES 对 InnoDB 锁时要注意,要将 AUTOCOMMIT 设为0,否则 MySQL 不会给表加锁。事务结束前,不要用UNLOCAK TABLES 释放表锁,因为 UNLOCK TABLES 会隐含地提交事务;COMMIT 或 ROLLBACK 不能释放用 LOCAK TABLES 加的表级锁,必须用 UNLOCK TABLES 释放表锁,正确的方式见如下语句:
例4. 如果需要写表t1并从表t2读,可以按如下做:
> SET AUTOCOMMIT=0; > LOCAK TABLES t1 WRITE, t2 READ, ...; > [do something with tables t1 and here]; > COMMIT; > UNLOCK TABLES;
四、死锁
MyISAM 表锁是 deadlock free 的,这是因为 MyISAM 总是一次性获得所需的全部锁,要么全部满足要么等待,因此不会出现死锁。但是在 InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了 InnoDB 发生死锁是可能的。
发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并退回,另一个事务获得锁,继续完成事务。如果出现死锁,可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因和改进措施。
但在涉及外部锁,或涉及锁的情况下(?),InnoDB 并不能完全自动检测到死锁,这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获取所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖垮数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句,绝大部分都可以避免。下面就通过实例来介绍几种死锁的常用方法:
-
在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序为访问表,这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可能避免。
-
在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低死锁的可能。
-
在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应该先申请共享锁,更新时再申请排他锁,甚至死锁。
-
在 REPEATEABLE-READ 隔离级别下,如果两个线程同时对相同条件记录用 SELECT...ROR UPDATE 加排他锁,在没有符合该记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成 READ COMMITTED,就可以避免问题。
-
当隔离级别为 READ COMMITED 时,如果两个线程都先执行 SELECT...FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁。
尽管通过上面的设计和优化等措施,可以大减少死锁,但死锁很难完全避免。因此,在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。