Mysql-锁

MySQL 锁

• thanks :

1. zhiboer
2. 静下心来静下心来

• 前提

• 没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。
• for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效
• MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。
• 排他锁包含行锁、表锁。

• 数据一致性

• 悲观锁

  • 共享锁

    共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。
    关键字：==lock in share mode==

    begin;
     SELECT * from city where id = "1"  lock in share mode;

  • 排他锁

    多个不同的事务，对同一个资源只能有一把锁。
    关键字：==for update==
    每次获取商品时，对该商品加排他锁。
    期间其他用户阻塞等待访问该记录。
    悲观锁适合写入频繁的场景。

    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. 只根据主键进行查询，并且查询到数据，主键字段产生行锁。
2. 除了使用非主键不含索引字段查询，其余所有的查询，没有查询到数据，就不产生锁。
3. 根据主键、非主键含索引（name）进行查询，并且查询到数据，主键字段产生行锁，name字段产生行锁。

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

4. 根据主键、非主键不含索引（name）进行查询，并且查询到数据，如果其他线程按主键字段进行再次查询，则主键字段产生行锁，如果其他线程按非主键不含索引字段进行查询，则非主键不含索引字段产生表锁，如果其他线程按非主键含索引字段进行查询，则非主键含索引字段产生行锁，如果索引值是枚举类型，mysql也会进行表锁。
5. 根据非主键含索引（name）进行查询，并且查询到数据，name字段产生行锁。
6. 根据非主键不含索引（name）进行查询，查到或查不到数据，name字段产生表锁。
7. 只根据主键进行查询，查询条件为不等于，查到或查不到数据，主键字段产生都表锁。
8. 只根据主键进行查询，查询条件为 like，查到或查不到数据，主键字段产生表锁。

• 总结

1. InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。==行锁一定有索引==
2. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
3. 如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。
4. 检索值的==数据类型==与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。