首页 > > 网络编程 > Mysql >

（转）MySQL锁

2018-06-18 00:28:27来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

一、MySQL锁概述

　　数据库锁机制简单来说，就是数据库为了保证数据并发访问的一致性、有效性，使得数据被并发访问变得有序所设计的一种规则。

　　由于MySQL有不同的存储引擎，而不同的存储引擎又采用不同的锁机制。比如：MyISAM存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持表级锁，又支持行级锁（row-level locking），默认情况下采用行级锁；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁。

　　下面来介绍下这三种锁：

　　1、表级锁（table-level locking）

　　表级锁是MySQL中最大粒度的锁定机制。特点是：开销最小、获取和释放锁的速度最快。由于锁定的是整张表，所以不会出现死锁。缺点是，发生资源争用的概率最高，并发量度大大降低。

　　2、行级锁（row-level locking）

　　行级锁是MySQL中最小粒度的锁定机制。特点是：发生资源争用的概率最低，并发度高。但是由于粒度最小，所以获取和释放锁的速度也慢，开销更大，而且容易出现死锁。

　　3、页级锁（page-level locking）

　　页级锁是MySQL中比较特殊的一种锁定机制，在其他数据库中不常见。它的锁定粒度在行级锁和表级锁之间，所以带来的开销和并发处理能力也在两者之间，而且也容易发生死锁。

　　应用场景：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如web应用；而行级锁更适应于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

二、表级锁

　　由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎。

　　1、MySQL表级锁的锁模式

　　MySQL的表级锁有两种模式：表共享读锁（Table Read Lock） 和表独占写锁（Table Write Lock）。锁模式的兼容性：

　　对MyISAM表的读操作，不会阻塞其他用户对表的读请求，但是会阻塞对同一表的写请求。

　　对MyISAM表的写操作，则会阻塞其他用户对表的读和写请求。

　　MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写操作后，只有持有锁的线程可以对表进行写操作。其他线程的读、写都会等待，直到锁被释放为止。

　　1.1 MyISAM存储引擎的写阻塞读例子

会话1 会话2

/*获得表auth_type的WRITE锁定*/

lock table auth_type write;

  

/*当前回话对锁定的表可以进行增删改查的操作*/

INSERT INTO `auth_type` (`id`, `type_code`, `type_name`) VALUES (1, '001', '一级用户');

受影响的行: 0；
select * from auth_type where id = 1;

id    type_code    type_name
1    001    一级用户
2    002    二级用户


INSERT INTO `auth_type` (`id`, `type_code`, `type_name`) VALUES (2, '001', '二级用户');

受影响的行: 0；
INSERT INTO `auth_type` (`id`, `type_code`, `type_name`) VALUES (3, '001', '三级用户');

受影响的行: 0；
UPDATE auth_type SET `type_code` = '002' WHERE id = 2;

受影响的行: 0；
DELETE FROM auth_type WHERE id = 3;

受影响的行: 0；

  
 

/*其他会话对锁定表的查询被阻塞，需要等待锁被释放*/

select * from auth_type where id = 1;

等待

/*释放锁*/

unlock tables;

 等待
 

/*会话2获得锁，查询返回结果*/

id    type_code    type_name
1    001    一级用户
2    002    二级用户

　　

　　1.2 如何加表锁

　　MyISAM在执行SELECT语句前，会自动给涉及的所有表加读锁，在执行更新语句（INSERT\UPDATE\DELETE等）前，会自动给涉及的表加写锁，并不需要向上面的例子一样人为的用LOCK TABLE 命令显示的加锁。这里只是为了演示而已。

　　1.2.1 在Lock tables read 时有一个“local”选项，如：lock table auth_type read local 其作用就是满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。

　　1.2.2 在用Lock tables给表显示加锁时，必须同时取得所有涉及表的锁，如果同一个表在SQL语句中出现多次，就要通过SQL语句中不同的别名锁定多次，否则也会出错。

　　如：lock tables auth_type as a read,auth_type as b read;

　　1.3 并发插入

　　MyISAM表的读和写是串行的，但这是就总体而言。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

　　MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

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

　　concurrent_insert = 1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表时，另一个进程从表尾插入记录。但是更新会等待。

　　concurrent_insert = 2时，无论表有没有空洞，都允许在表尾并发插入记录。但是更新会等待。

　　可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用问题。

　　1.4 MyISAM的锁调度

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

  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

　　虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

　　另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

　　上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。
 
三、InnoDB锁问题
　　1、InnoDB的行锁模式及加锁方法
　　InnoDB实现了以下两种类型的行锁：
　　共享锁（S）：锁粒度是行或者元组（多行）。一个事务获取了共享锁后，可以对锁定范围内的数据执行读操作。如果事务1获得一个元组的共享锁，事务2还可以立即获得这个元组的共享锁，但是不能立即获取这个元组的排他锁，必须等到事务1释放共享锁之后。
　　排他锁（X）：锁粒度和共享锁相同。一个事务获取了排他锁之后，可以对锁定范围内的数据执行写操作。如果事务1获得一个元组的排他锁，事务2不能立即获得这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到事务1释放排他锁之后。
　　意向锁：一种表锁，锁定的粒度是整张表，分为意向共享锁（IS）和意向排他锁（IX）两类。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。“有意”这两个字的意思就是，事务想干这个事但是还没干。举例说明下意向共享锁，比如一个事务t执行了这样一个语句：select * from table lock in share model ，如果这个语句执行成功，就对表table上了一个意向共享锁。lock in share model就是说事务t1在接下来要执行的语句中要获取S锁。如果t1的select * from table lock in share model执行成功，那么接下来t1应该可以畅通无阻的去执行只需要共享锁的语句了。意向排它锁的含义同理可知，上例中要获取意向排它锁，可以使用select * from table for update 。
　　2、锁的互斥与兼容关系
　　锁和锁之间的关系，要么相容，要么互斥。
　　锁a和锁b相容是指：操作相同一组数据时，如果事务1获取了锁a，另一个事务2还可以获取锁b。
　　锁a和锁b互斥是指：操作相同一组数据时，如果事务1获取了锁a，另一个事务2在事务1释放锁a之前无法获取锁b。
　　
  X S IX IS
X n n n n
S n y n y
IX n n y y
IS n y y y

 

　　解析：

　　事务1获取了X锁，事务2无法立即获取X锁、S锁、IX锁和IS锁。

　　事务1获取了S锁，事务2无法立即获取X锁和IX锁，可以立即获取S锁和IS锁。

　　事务1获取IX锁，事务2无法立即获取X锁和S锁，可以立即获取IX锁和IS锁。

　　事务1获取IS锁，事务2无法立即获取X锁，可以立即获取S锁、IX锁和IS锁。

　　意向锁是InnoDB自动加的，不需要用户干预。对于UPDATE\DELETE\INSERT语句，InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句给记录集加共享锁或者排他锁。

　　S锁：　SELECT * FROM auth_type WHERE ... LOCK IN SHARE MODE

　　X锁：   SELECT * FROM auth_type WHERE ... FOR UPDATE

　　用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

　　3、InnoDB行锁实现方式

　　InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

　　（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

　　　　　　　　　　没有设置索引的情况

会话1
会话2
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 1 ;
1 row in set (0.00 sec)
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 2 ;
1 row in set (0.00 sec)
select * from auth_type where id = 1 for update;
1 row in set (0.00 sec)
  
 
select * from auth_type where id = 2 for update;
等待

 

 

　　　　　　　　有设置索引的情况

会话1
会话2
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 1 ;
1 row in set (0.00 sec)
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 2 ;
1 row in set (0.00 sec)
select * from auth_type where id = 1 for update;
1 row in set (0.00 sec)
  
 
select * from auth_type where id = 2 for update;
1 row in set (0.00 sec)

　　（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

会话1
会话2
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 1 ;
1 row in set (0.00 sec)
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 2 ;
1 row in set (0.00 sec)
select * from auth_type where id = 1 for update;
1 row in set (0.00 sec)
  
 
select * from auth_type where id = 1 AND auth_type = '001' for update;
等待

　　（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

　　　　　　　　对name也加了索引

会话1
会话2
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 1 ;
1 row in set (0.00 sec)
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
select * from auth_type where id = 2 ;
1 row in set (0.00 sec)
select * from auth_type where id = 1 for update;
1 row in set (0.00 sec)
  
 
select * from auth_type where id = 2 for update;
1 row in set (0.00 sec)
 
select * from auth_type where  auth_type = '001' for update;
等待(该记录被会话1锁定，所以等待获得锁)

　　（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

四 间隙锁（Next-key lock）

　　当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

　　举例来说，假如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除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
　　
会话1
会话2
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
当前session对不存在的记录加for update的锁：
mysql> select * from emp where empid = 102 for update;
Empty set (0.00 sec)
  
 
这时，如果其他session插入empid为102的记录（注意：这条记录并不存在），也会出现锁等待：
mysql>insert into emp(empid,...) values(102,...);
阻塞等待
Session_1 执行rollback：
mysql> rollback;
Query OK, 0 rows affected (13.04 sec)
  
 
由于其他session_1回退后释放了Next-Key锁，当前session可以获得锁并成功插入记录：
mysql>insert into emp(empid,...) values(102,...);
Query OK, 1 row affected (13.35 sec)
五 恢复和复制的需要，对InnoDB锁机制的影响
　　MySQL通过Binlog录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。MySQL的恢复机制（复制其实就是在Slave Mysql不断做基于BINLOG的恢复）有以下特点。
　　一是MySQL的恢复是SQL语句级的，也就是重新执行Binlog中的SQL语句。
　　二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。
　　从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的，有关InnoDB在不同隔离级别下加锁的差异在下一小节还会介绍。
　　另外，对于“insert  into target_tab select * from source_tab where ...”和“create  table new_tab ...select ... From  source_tab where ...(CTAS)”这种SQL语句，用户并没有对source_tab做任何更新操作，但MySQL对这种SQL语句做了特别处理。
会话1
会话2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = '1';
+----+------+----+
| d1 | name | d2 |
+----+------+----+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+----+------+----+
5 rows in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = '1';
+----+------+----+
| d1 | name | d2 |
+----+------+----+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+----+------+----+
5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = '1';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
  
 
mysql> update source_tab set name = '1' where name = '8';
等待
commit;
  
 
返回结果
commit;

 　　在上面的例子中，只是简单地读 source_tab表的数据，相当于执行一个普通的SELECT语句，用一致性读就可以了。RACLE正是这么做的，它通过MVCC技术实现的多版本数据来实现一致性读，不需要给source_tab加任何锁。我们知道InnoDB也实现了多版本数据，对普通的SELECT一致性读，也不需要加任何锁；但这里InnoDB却给source_tab加了共享锁，并没有使用多版本数据一致性读技术！

　　MySQL为什么要这么做呢？其原因还是为了保证恢复和复制的正确性。因为不加锁的话，如果在上述语句执行过程中，其他事务对source_tab做了更新操作，就可能导致数据恢复的结果错误。为了演示这一点，我们再重复一下前面的例子，不同的是在session_1执行事务前，先将系统变量 innodb_locks_unsafe_for_binlog的值设置为“on”（其默认值为off），具体结果如下表所示。
　　
会话1
会话2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql>set innodb_locks_unsafe_for_binlog='on'
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = '1';
+----+------+----+
| d1 | name | d2 |
+----+------+----+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+----+------+----+
5 rows in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = '1';
+----+------+----+
| d1 | name | d2 |
+----+------+----+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+----+------+----+
5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = '1';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
  
 
session_1未提交，可以对session_1的select的记录进行更新操作。
mysql> update source_tab set name = '8' where name = '1';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0
mysql> select * from source_tab where name = '8';
+----+------+----+
| d1 | name | d2 |
+----+------+----+
|  4 | 8    |  1 |
|  5 | 8    |  1 |
|  6 | 8    |  1 |
|  7 | 8    |  1 |
|  8 | 8    |  1 |
+----+------+----+
5 rows in set (0.00 sec)
 
更新操作先提交
mysql> commit;
Query OK, 0 rows affected (0.05 sec)
插入操作后提交
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
  
此时查看数据，target_tab中可以插入source_tab更新前的结果，这符合应用逻辑：
mysql> select * from source_tab where name = '8';
+----+------+----+
| d1 | name | d2 |
+----+------+----+
|  4 | 8    |  1 |
|  5 | 8    |  1 |
|  6 | 8    |  1 |
|  7 | 8    |  1 |
|  8 | 8    |  1 |
+----+------+----+
5 rows in set (0.00 sec)
mysql> select * from target_tab;
+------+------+
| id   | name |
+------+------+
| 4    | 1.00 |
| 5    | 1.00 |
| 6    | 1.00 |
| 7    | 1.00 |
| 8    | 1.00 |
+------+------+
5 rows in set (0.00 sec)
mysql> select * from tt1 where name = '1';
Empty set (0.00 sec)
mysql> select * from source_tab where name = '8';
+----+------+----+
| d1 | name | d2 |
+----+------+----+
|  4 | 8    |  1 |
|  5 | 8    |  1 |
|  6 | 8    |  1 |
|  7 | 8    |  1 |
|  8 | 8    |  1 |
+----+------+----+
5 rows in set (0.00 sec)
mysql> select * from target_tab;
+------+------+
| id   | name |
+------+------+
| 4    | 1.00 |
| 5    | 1.00 |
| 6    | 1.00 |
| 7    | 1.00 |
| 8    | 1.00 |
+------+------+
5 rows in set (0.00 sec)

 　　　从上可见，设置系统变量innodb_locks_unsafe_for_binlog的值为“on”后，InnoDB不再对source_tab加锁，结果也符合应用逻辑，但是如果分析BINLOG的内容：

　

......
SET TIMESTAMP=1169175130;
BEGIN;
# at 274
#070119 10:51:57 server id 1  end_log_pos 105   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1169175117;
update source_tab set name = '8' where name = '1';
# at 379
#070119 10:52:10 server id 1  end_log_pos 406   Xid = 5
COMMIT;
# at 406
#070119 10:52:14 server id 1  end_log_pos 474   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1169175134;
BEGIN;
# at 474
#070119 10:51:29 server id 1  end_log_pos 119   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1169175089;
insert into target_tab select d1,name from source_tab where name = '1';
# at 593
#070119 10:52:14 server id 1  end_log_pos 620   Xid = 7
COMMIT;
......
可以发现，在BINLOG中，更新操作的位置在INSERT...SELECT之前，如果使用这个BINLOG进行数据库恢复，恢复的结果与实际的应用逻辑不符；如果进行复制，就会导致主从数据库不一致！
通过上面的例子，我们就不难理解为什么MySQL在处理“Insert  into target_tab select * from source_tab where ...”和“create  table new_tab ...select ... From  source_tab where ...”时要给source_tab加锁，而不是使用对并发影响最小的多版本数据来实现一致性读。还要特别说明的是，如果上述语句的SELECT是范围条件，InnoDB还会给源表加间隙锁（Next-Lock）。
因此，INSERT...SELECT...和 CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。
如果应用中一定要用这种SQL来实现业务逻辑，又不希望对源表的并发更新产生影响，可以采取以下两种措施：
一是采取上面示例中的做法，将innodb_locks_unsafe_for_binlog的值设置为“on”，强制MySQL使用多版本数据一致性读。但付出的代价是可能无法用binlog正确地恢复或复制数据，因此，不推荐使用这种方式。
二是通过使用“select * from source_tab ... Into outfile”和“load data infile ...”语句组合来间接实现，采用这种方式MySQL不会给source_tab加锁。

六、InnoDB在不同隔离级别下的一致性读及锁的差异

　　

Read Uncommitted（读取未提交内容）

       在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（读取提交内容）

       这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（可重读）

       这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）
       这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

         这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

         脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

         不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

         幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

                                  InnoDB存储引擎中不同SQL在不同隔离级别下锁比较
隔离级别
        一致性读和锁
SQL
Read Uncommited
Read Commited
Repeatable Read
Serializable
SQL
条件
        
select
相等
None locks
Consisten read/None lock
Consisten read/None lock
Share locks
范围
None locks
Consisten read/None lock
Consisten read/None lock
Share Next-Key
update
相等
exclusive locks
exclusive locks
exclusive locks
Exclusive locks
范围
exclusive next-key
exclusive next-key
exclusive next-key
exclusive next-key
Insert
N/A
exclusive locks
exclusive locks
exclusive locks
exclusive locks
replace
无键冲突
exclusive locks
exclusive locks
exclusive locks
exclusive locks
键冲突
exclusive next-key
exclusive next-key
exclusive next-key
exclusive next-key
delete
相等
exclusive locks
exclusive locks
exclusive locks
exclusive locks
范围
exclusive next-key
exclusive next-key
exclusive next-key
exclusive next-key
Select ... from ... Lock in share mode
相等
Share locks
Share locks
Share locks
Share locks
范围
Share locks
Share locks
Share Next-Key
Share Next-Key
Select * from ... For update
相等
exclusive locks
exclusive locks
exclusive locks
exclusive locks
范围
exclusive locks
Share locks
exclusive next-key
exclusive next-key
Insert into ... Select ...
（指源表锁）
innodb_locks_unsafe_for_binlog=off
Share Next-Key
Share Next-Key
Share Next-Key
Share Next-Key
innodb_locks_unsafe_for_binlog=on
None locks
Consisten read/None lock
Consisten read/None lock
Share Next-Key
create table ... Select ...
（指源表锁）
innodb_locks_unsafe_for_binlog=off
Share Next-Key
Share Next-Key
Share Next-Key
Share Next-Key
innodb_locks_unsafe_for_binlog=on
None locks
Consisten read/None lock
Consisten read/None lock
Share Next-Key
　　从上表可以看出：对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行set session transaction isolation level repeatable read或set session transaction isolation level serializable动态改变隔离级别的方式满足需求。

七、什么时候使用表锁

　　对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

　　第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

　　 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

　　当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

　　在InnoDB下，使用表锁要注意以下两点。

　　（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。有关死锁，下一小节还会继续讨论。
　　（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：
例如，如果需要写表t1并从表t读，可以按如下做：
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

八、关于死锁

　　上文讲过，MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。

　　但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。

　　InnoDB存储引擎中的死锁例子

会话1
会话2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_1 where where id=1 for update;
...
做一些其他处理...
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_2 where id=1 for update;
...
select * from table_2 where id =1 for update;
因session_2已取得排他锁，等待
做一些其他处理...
 
mysql> select * from table_1 where where id=1 for update;
死锁

　　在上面的例子中，两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。

　　发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

　　通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。

　　（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。如上面的例子，就是访问两个表的顺序不同造成的死锁。

　　（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。如下表：

会话1
会话2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_1 where where id=1 for update;
...
做一些其他处理...
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_1 where id=3 for update;
...
select * from table_1 where id =3 for update;
因session_2已取得排他锁，等待
做一些其他处理...
 
mysql> select * from table_1 where where id=1 for update;
死锁

　　（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

　　（4）前面讲过，在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

　　　　　　　　　

会话1
会话2
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
当前session对不存在的记录加for update的锁：
mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)
  
 
其他session也可以对不存在的记录加for update的锁：
mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)
因为其他session也对该记录加了锁，所以当前的插入会等待：
mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom');
等待
  
 
因为其他session已经对记录进行了更新，这时候再插入记录就会提示死锁并退出：
mysql> insert into actor (actor_id, first_name , last_name) values(201,'Lisa','Tom');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
由于其他session已经退出，当前session可以获得锁并成功插入记录：
mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom');
Query OK, 1 row affected (13.35 sec)

（5）当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。

会话1
会话2
会话3
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
Session_1获得for update的共享锁：
mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)
由于记录不存在，session_2也可以获得for update的共享锁：
mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)
  
Session_1可以成功插入记录：
mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');
Query OK, 1 row affected (0.00 sec)
    
 
Session_2插入申请等待获得锁：
mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');
等待
  
Session_1成功提交：
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
    
 
Session_2获得锁，发现插入记录主键重，这个时候抛出了异常，但是并没有释放共享锁：
mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');
ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'
  
   
Session_3申请获得共享锁，因为session_2已经锁定该记录，所以session_3需要等待：
mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;
等待
 
这个时候，如果session_2直接对记录进行更新操作，则会抛出死锁的异常：
mysql> update actor set last_name='Lan' where actor_id = 201;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  
   
Session_2释放锁后，session_3获得锁：
mysql> select first_name, last_name from actor where actor_id = 201 for update;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Lisa       | Tom       |
+------------+-----------+
1 row in set (31.12 sec)
　　尽管通过上面介绍的设计和SQL优化等措施，可以大大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

　　如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

　　

mysql> show innodb status \G
…….
------------------------
LATEST DETECTED DEADLOCK
------------------------
070710 14:05:16
*** (1) TRANSACTION:
TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS thread id 1197328736 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216
MySQL thread id 7521657, query id 673468054 localhost root update
insert into country (country_id,country) values(110,'Test')
………
*** (2) TRANSACTION:
TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS thread id 1164048736 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 7521664, query id 673468058 localhost root statistics
select first_name,last_name from actor where actor_id = 1 for update
*** (2) HOLDS THE LOCK(S):
………
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
………
*** WE ROLL BACK TRANSACTION (1)

……

 

九 小结

本章重点介绍了MySQL中MyISAM表级锁和InnoDB行级锁的实现特点，并讨论了两种存储引擎经常遇到的锁问题和解决办法。
对于MyISAM的表锁，主要讨论了以下几点：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。
对于InnoDB表，本章主要讨论了以下几项内容。
l         InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
l         介绍了InnoDB间隙锁（Next-key)机制，以及InnoDB使用间隙锁的原因。
l         在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
l         MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。
l         锁冲突甚至死锁很难完全避免。
在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：
l         尽量使用较低的隔离级别；
l         精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
l         选择合理的事务大小，小事务发生锁冲突的几率也更小；
l         给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
l         不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
l         尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；
l         不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
l         对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。
 
转自：http://blog.csdn.net/xifeijian/article/details/20313977

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：记一次mysql千万订单汇总查询优化

下一篇：MySQL学习之一数据库简介

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签