Mysql InnoDB 排他锁
用法： select … for update;

例如：select * from goods where id = 1 for update;

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

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

mysql悲观锁
悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

MySQL SELECT … FOR UPDATE 的Row Lock 与Table Lock

mysql 乐观锁
乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。那么我们如何实现乐观锁呢，一般来说有以下2种方式：

1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

两种解决方案：

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

begin;
select * from goods where id = 1 for update;
update goods set stock = stock - 1 where id = 1;
commit;

乐观锁方案：每次获取商品时，不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。乐观锁适合读取频繁的场景。

#不加锁获取 id=1 的商品对象
select * from goods where id = 1

begin;
#更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新
update goods set stock = stock - 1 where id = 1 and stock = cur_stock;
commit;

二、行锁与表锁

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

begin;
select * from goods where id = 1 for update;
commit;

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

begin;
select * from goods where id = 1 for update;
commit;

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

begin;
select * from goods where id = 1 and name=’prod11’ for update;
commit;

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

begin;
select * from goods where id = 1 and name=’prod12’ for update;
commit;

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

begin;
select * from goods where id = 1 and name=’prod11’ for update;
commit;

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

begin;
select * from goods where id = 1 and name=’prod12’ for update;
commit;

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

begin;
select * from goods where name=’prod11’ for update;
commit;

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

begin;
select * from goods where name=’prod11’ for update;
commit;

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

begin;
select * from goods where name=’prod11’ for update;
commit;

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

begin;
select * from goods where name=’prod11’ for update;
commit;
11、只根据主键进行查询，查询条件为不等于，并且查询到数据，主键字段产生表锁。

begin;
select * from goods where id <> 1 for update;
commit;

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

begin;
select * from goods where id <> 1 for update;
commit;

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

begin;
select * from goods where id like ‘1’ for update;
commit;

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

begin;
select * from goods where id like ‘1’ for update;
commit;

1、InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

2、由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
3、当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
4、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
5、检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

WHERE条件使用主键，SELECT … FOR UPDATE为行级锁；
WHERE条件使用唯一索引，SELECT … FOR UPDATE为行级锁；
WHERE条件使用普通索引，SELECT … FOR UPDATE为行级锁；
WHERE条件使用联合索引的前缀索引，SELECT … FOR UPDATE为行级锁；
WHERE条件不使用索引，SELECT … FOR UPDATE为表级锁；
即：WHERE条件能使用索引时，SELECT … FOR UPDATE表现为行级锁；WHERE条件不使用索引，SELECT … FOR UPDATE表现为表级锁；