MySQL锁简介

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

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

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

前提准备：

1. 提前建一个测试用表

CREATE TABLE `lock_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('4', 'd');

INSERT INTO `user` (`id`, `NAME`) VALUES ('1', 'zhangsan');

2.开启两个mysql回话，并关闭autocommit

set @@autocommit=OFF;

MyISAM表锁

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

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

写锁阻塞读的案例

会话1	会话2
获取表写锁 lock table lock_table write;
当前会话对表增删改查都可以执行 select * from lock_table; insert into lock_table values(5,'e'); delete from lock_table where id=5;	当前会话对表的查询操作会被阻塞 select * from lock_table;
释放锁 unlock tables;	会话1释放锁之后当前会话能够立即执行，并查询出结果

读锁阻塞写的案例

会话1	会话2
获取表读锁 lock table lock_table read;
当前会话可以查询数据 select * from lock_table;	当前会话可以查询数据 select * from lock_table;
当前会话不能查询没有锁定的表 select * from user; ERROR 1100 (HY000): Table 'user' was not locked with LOCK TABLES	当前会话可以查询或者更新未锁定的表 select * from lock_table; select * from user; insert into user (id, NAME) VALUES ('4', 'wangwu');
当前会话进行插入或更新表会提示错误 update lock_table set name='aa' where id =1; ERROR 1099 (HY000): Table 'lock_table' was locked with a READ lock and can't be updated insert into lock_table values(9,'i'); ERROR 1099 (HY000): Table 'lock_table' was locked with a READ lock and can't be updated	当前会话可读取 select * from lock_table; 当前会话进行写操作会阻塞等待获得锁 update lock_table set name='aa' where id =1;
释放锁 unlock tables;	获得锁，更新成功

总结

MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁，上例中的加锁时为了演示效果。

InnoDB锁

1. 事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2.并发情况下事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读： 一个事务在更新一条记录，未提交前，第二个事务读到了第一个事务更新后的记录，那么第二个事务就读到了脏数据，会产生对第一个未提交数据的依赖。一旦第一个事务回滚，那么第二个事务读到的数据，将是错误的脏数据。

不可重复读：一个事务在读取某些数据后的一段时间后，再次读取这个数据，发现其读取出来的数据内容已经发生了改变，就是不可重复读。

幻读： 一个事务按相同的查询条件查询之前检索过的数据，确发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

隔离级别	脏读	幻读	不可重复读
Read Uncommitted（读取未提交内容）	√	√	√
Read Committed（读取提交内容）		√	√
Repeatable Read（可重读）		√
Serializable（可串行化）

3.InnoDB行锁及加锁方法

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，但是组织其他事务获得相同数据集的共享锁和排他锁。
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

MySQL InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

InnoDB行锁实现方式

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

1.在不通过索引查询的时候，innodb使用的是表锁而不是行锁
创建表

CREATE TABLE `person_no_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into person_no_index (id,name,age) values (1,"张三",25);
insert into person_no_index (id,name,age) values (2,"李四",28);
insert into person_no_index (id,name,age) values (3,"赵六",59);

会话1	会话2
begin; select * from person_no_index where id = 1 for update;	begin; select * from person_no_index where id = 2 for update; 此时该会话会阻塞等待排它锁释放
commit;	会话1释放排它锁之后可查询出数据

会话1只给一行加了排他锁，但是会话2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2.通过索引查询的时候，innodb使用的是行锁
创建表

CREATE TABLE `person_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `name_idx` (`name`),
  KEY `age_idx` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into person_index (id,name,age) values (1,"张三",25);
insert into person_index (id,name,age) values (2,"李四",28);
insert into person_index (id,name,age) values (3,"赵六",59);

会话1	会话2
begin; select * from person_index where name = '张三' for update;
	begin; select * from person_index where name = '李四' for update;（此时可以查询出数据） select * from person_index where name = '张三' for update;（此时进入阻塞状态，等待会话1释放锁）
commit;	会话1释放排它锁之后可查询出name='张三'的数据

会话1只给 name=张三 的行加了排他锁，会话2在请求其他行的排他锁的时候，可以正常查询，在查询 name=张三 时出现锁阻塞，所以此时使用的是行锁。

2.由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现冲突的。

插入一条name=张三 id不同的数据
insert into person_index (id,name,age) values (4,"张三",88);
此时表中数据为：
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 张三   |   25 |
|    2 | 李四   |   28 |
|    3 | 赵六   |   59 |
|    4 | 张三   |   88 |
+------+--------+------+

会话1	会话2
begin; select * from person_index where id=1 and name='张三' for update;
	begin; select * from person_index where id=4 and name='张三' for update; name字段有索引，虽然查询的数据是不同行记录，但是因为索引键一样，会出现阻塞
commit;	会话1释放锁后可查询出数据

总结

对于MyISAM的表锁，主要讨论了以下几点：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，本文主要讨论了以下几项内容：
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。