MySQL的锁机制研究

基础准备

理论知识

MyISAM引擎的锁

MyISAM只支持表锁，锁的行为如下所述：

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

• 对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；

• MyISAM表的读操作和写操作之间，以及写操作之间是串行的。

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

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

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

• 通过执行命令SET LOWPRIORITYUPDATES=1，使该连接发出的更新请求优先级降低。

• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。 另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数maxwritelock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。

InnoDB引擎的锁

InnoDB支持事务和行锁，但是引入了一些新的问题。

事务及ACID

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

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

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

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

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

并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题

• 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。

• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

事务４种隔离级别比较

InnoDB行锁的实现方式

InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！ 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

试验步骤

创建两个表

CREATE TABLE `tbl_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
  `dept_id` int(11) NOT NULL COMMENT '部门编号',
  `dept_name` varchar(32) NOT NULL COMMENT '部门名称',
  `dept_status` char(1) NOT NULL COMMENT '状态；0-正常；1-停用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';

CREATE TABLE `tbl_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
  `emp_id` int(11) NOT NULL COMMENT '员工ID',
  `dept_id` int(11) NOT NULL COMMENT '所属部门ID，关联tbl_dept的dept_id字段',
  `emp_name` varchar(32) NOT NULL COMMENT '员工名字',
  `emp_salary` int(11) NOT NULL COMMENT '工资',
  `emp_status` char(1) NOT NULL COMMENT '员工状态；0-正常；1-离职',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

准备数据

insert into tbl_dept(dept_id,dept_name,dept_status) values(1,'总裁办','0'),(2,'财务部','0'),(3,'人力资源部','0'),(4,'市场部','0'),(5,'技术部','0');

insert into tbl_emp(emp_id,dept_id,emp_name,emp_salary,emp_status) values
(1,1,'James',100000,'0'),
(2,1,'Tom',90000,'0'),
(3,1,'Mike',80000,'0'),
(4,2,'tim',80000,'0'),
(5,2,'kobe',80000,'0'),
(6,3,'kate',80000,'0'),
(7,3,'green',80000,'0'),
(8,4,'park',80000,'0'),
(9,4,'manu',80000,'0'),
(10,5,'martin',80000,'0'),
(11,5,'fish',80000,'0');

试验过程

查询数据库的事务隔离级别

SELECT @@tx_isolation

REPEATABLE-READ

MySQL默认的是可重复读级别，不会出现“脏读”和”不可重复读“，但是会出现”幻读“。

试验1：验证使用索引列的情况下InnoDB使用行锁

分别开启两个事物，关闭自动提交。 在第一个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where id = 5;

没有其他锁的情况下，该语句执行成功，等待提交或者回滚。

在第二个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where id = 5;

该语句等待锁，可以发现Innodbrowlockcurrentwaits变成了1，知道第一个事务提交或回滚后，这个事务获取锁，可以继续执行。

如果在第二个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where id = 6;

该语句执行成功，等待提交或者回滚，由于第一个事务锁上了id=5的行，对id=6的行无影响。

实现1结果：行锁生效

条件带有索引列的情况下，如果是更新同一条数据，第一个事务没有提交或者回滚的情况下，第二个事务等待，可以看到Innodbrowlockcurrentwaits变为1;如果是更新不同的数据，两个事务均能执行成功，证明事务用了行锁。

试验2：验证不使用索引列的情况下InnoDB使用表锁

分别开启两个事物，关闭自动提交。 在第一个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where emp_id = 5;

没有其他锁的情况下，该语句执行成功，等待提交或者回滚。

在第二个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where emp_id = 5;

该语句等待锁，可以发现Innodbrowlockcurrentwaits变成了1，知道第一个事务提交或回滚后，这个事务获取锁，可以继续执行。

如果在第二个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where emp_id = 6;

该语句等待锁，该语句等待锁，可以发现Innodbrowlockcurrentwaits变成了1，知道第一个事务提交或回滚后，这个事务获取锁，可以继续执行。

实现2结果：表锁生效

条件没有带索引列的情况下，不管是不是更新同一条数据，都会导致第二个事务等锁，只有第一个事务提交或混滚后，第二个事务才能执行，证明事务用了表锁。

试验3：验证条件带有索引列和非索引列的情况下InnoDB使用行锁

分别开启两个事物，关闭自动提交。 在第一个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where id = 5 and emp_id = 5;

没有其他锁的情况下，该语句执行成功，等待提交或者回滚。

在第二个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where id=5 and emp_id = 5;

该语句等待锁，可以发现Innodbrowlockcurrentwaits变成了1，知道第一个事务提交或回滚后，这个事务获取锁，可以继续执行。

如果在第二个事务中执行以下操作：

begin transaction;
update tbl_emp set user_status = '1' where id= 6 and emp_id = 6;

该语句执行成功，等待提交或者回滚，由于第一个事务锁上了id=5的行，对id=6的行无影响。

实现3结果：行锁生效

条件带有索引列和非索引列的情况下，如果不是操作同一条数据，两个事务可以并发进行，证明使用了行锁。

查看mysql的锁统计

show status like '%row_lock%';
'Innodb_row_lock_current_waits', '0'
'Innodb_row_lock_time', '571953'
'Innodb_row_lock_time_avg', '203'
'Innodb_row_lock_time_max', '51006'
'Innodb_row_lock_waits', '2810'

死锁等待时间

show variables like 'innodb_lock_wait_timeout';

在不能获取锁的情况下，事务默认等待时间50s，修改这个变量可以改变默认值。