老司机带大家领略MySQL中的乐观锁和悲观锁

为什么需要锁

在并发环境下，如果多个客户端访问同一条数据，此时就会产生数据不一致的问题，如何解决，通过加锁的机制，常见的有两种锁，乐观锁和悲观锁，可以在一定程度上解决并发访问。

乐观锁

乐观锁，顾名思义，对加锁持有一种乐观的态度，即先进行业务操作，不到最后一步不进行加锁，"乐观"的认为加锁一定会成功的，在最后一步更新数据的时候在进行加锁，乐观锁的实现方式一般为每一条数据加一个版本号，具体流程是这样的：

    1）、创建一张表时添加一个version字段，表示是版本号，如下：

    2）、修改数据的时候首先把这条数据的版本号查出来，update时判断这个版本号是否和数据库里的一致，如果一致则表明这条数据没有被其他用户修改，若不一致则表明这条数据在操作期间被其他客户修改过，此时需要在代码中抛异常或者回滚等。伪代码如下：

update tb set name='yyy' and version=version+1 where id=1 and version=version;

1. SELECT name AS old_name, version AS old_version FROM tb where ...;
2. 根据获取的数据进行业务操作，得到new_dname和new_version
3. UPDATE SET name = new_name, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试
}

    update其实在不在事物中都无所谓，在内部是这样的：update是单线程的，及如果一个线程对一条数据进行update操作，会获得锁，其他线程如果要对同一条数据操作会阻塞，直到这个线程update成功后释放锁。

    另外，乐观锁不需要数据库底层的支持！！！

悲观锁

    正如其名字一样，悲观锁对数据加锁持有一种悲观的态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

首先我们需要set autocommit=0，即不允许自动提交

用法：select * from tablename where id = 1 for update;

申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。

for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

假设有一张商品表 shop，它包含 id，商品名称，库存量三个字段，表结构如下：

插入如下数据：

并发导致数据一致性的问题：

如果有A、B两个用户需要购买id=1的商品，AB都查询商品数量是1000，A购买后修改商品的数量为999，B购买后修改数量为999。

用乐观锁的解决方案：

每次获取商品时，不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。伪代码如下：

//不加锁
select id,name,stock where id=1;

//业务处理

begin;

update shop set stock=stock-1 where id=1 and stock=stock;

commit;

悲观锁方案：

每次获取商品时，对该商品加排他锁。也就是在用户A获取获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录。悲观锁适合写入频繁的场景。代码如下：

begin;

select id,name,stock as old_stock from shop  where id=1 for update;

update shop set stock=stock-1 where id=1 and stock=old_stock;

commit;

我们可以看到，首先通过begin开启一个事物，在获得shop信息和修改数据的整个过程中都对数据加锁，保证了数据的一致性。

行锁与表锁

下面开启两个客户端模拟两个用户同时竞争数据。

1、只根据主键进行查询，并且查询到数据，主键字段产生行锁。

可以看到：id是主键，当在client1上查询id=1的数据时候，在client2上查询id=2的数据没问题，但在client2上查询id=1的数据时阻塞，说明此时的锁时行锁。当client1执行commit时，clinet2查询的id=1的命令立即返回数据。

2、只根据主键进行查询，没有查询到数据，不产生锁。

可以看到：在client1上查询一条不存在的数据时不会产生任何锁。

3、根据主键、非主键含索引（name）进行查询，并且查询到数据，主键字段产生行锁，name字段产生表锁。

如果我们在client1中执行：

select * from shop where id=1 and name='prod11' for update; 

查询到数据后，在client2和client3分别执行：

select * from shop where id=1 for update;
和
select * from shop where name='prod11' for update;和select * from shop where name='xxx' for update;

都会阻塞；

4、根据主键、非主键含索引（name）进行查询，没有查询到数据，不产生锁。

我们在name字段创建idxname的索引：

alter table shop add index idxName(name);

后没有查询到数据，在client2中id和name字段不会产生锁。

5、根据主键、非主键不含索引（name）进行查询，并且查询到数据，如果其他线程按主键字段进行再次查询，则主键字段产生行锁，如果其他线程按非主键不含索引字段进行查询，则非主键不含索引字段产生表锁，如果其他线程按非主键含索引字段进行查询，则非主键含索引字段产生行锁，如果索引值是枚举类型，mysql也会进行表锁，大家仔细理解一下。

这里就不截图了，大家自己试验。。。

6、根据主键、非主键不含索引（name）进行查询，没有查询到数据，不产生锁。

7、根据非主键含索引（name）进行查询，并且查询到数据，name字段产生行锁，将整行锁住，其他条件查询该数据阻塞。

我们在client1用name='prod11'查询到数据后，在client2中name字段会有行锁，prod12可以返回，prod11阻塞。

8、根据非主键含索引（name）进行查询，没有查询到数据，不产生锁。

9、根据非主键不含索引（name）进行查询，并且查询到数据，name字段产生表锁。

删除name字段的索引idxname;

alter table shop drop index idxname

可以看到，client1通过非索引的name字段查询到prod11的数据后，在client2查prod12的数据会阻塞，产生表锁。

10、根据非主键不含索引（name）进行查询，没有查询到数据，name字段产生表锁。

client1并没有查询到数据，client2任然会产生表锁。

11、只根据主键进行查询，查询条件为不等于，并且查询到数据，主键字段产生表锁。

client1通过！=进行范围查询且查询到数据，client2产生表锁。

12、只根据主键进行查询，查询条件为不等于，没有查询到数据，主键字段产生表锁。

13、只根据主键进行查询，查询条件为 like，并且查询到数据，主键字段产生表锁。

14、只根据主键进行查询，查询条件为 like，没有查询到数据，主键字段产生表锁。

11—14都是表锁，因为MySQL不可能通过范围查询查询到的每一条数据都加行锁，太消耗性能，还不如锁表呢（个人理解）。