MySQL中的表锁,行锁,排它锁,共享锁

表锁与行锁

1 ） 概念

• 在使用mysql的时候，如果同时向 mysql 里边批量进行更新, 插入或删除动作
• 数据库里的数据不会出问题, 在 mysql内部，它其实自带了一个锁的功能
• 而它内部有的是用了锁，有的没有用锁，没用锁的需要咱们利用锁来自行处理
• mysql 从范围的角度来讲，支持表锁和行锁
  • 表锁: 把整个表锁住
    • 你对这张表的任意行在做操作，你都得阻塞住
    • 还有就是, 占用这把锁的那个人处理完了下一个才能进来
  • 行锁: 它只锁了某一行
    • 表里的某一行, 比如你操作申请到锁了，下一个人来处理这一行, 它就会阻塞住
    • 如果下个人处理的是别的行，是允许的
  • 一般而言，行级锁是更好一些的锁
    • 因为我用哪个数据，我就锁住哪些数据即可
    • 当然也会有一些特殊的情况需要表级锁
• 创建mysql的表的时候，是可以指定引擎的
  • Myisam 引擎: 只支持表锁，不支持行锁
    • 无论怎么加锁都是行锁
  • InnoDB 引擎: 支持行锁和表锁
    • 如果进行搜索时，利用索引的形式去搜，也就是命中索引
    • 搜到的某一行数据或某些个数据，它就可以利用行锁
    • 如果搜索的时候没有用到索引，比如说全表扫描
    • 那它会对整个表都锁住，就应用表锁了
• 为了能够去用锁的时候，力度更细，InnoDB 是一个很好的选择
• 所以，项目开发中，我们都选择去使用 InnoDB 引擎
• 对 InnoDB 引擎创建的表，如果对这张表进行 update、insert、delete 做行为内部
• 实际上它会先申请锁，然后再去执行，执行完，再释放锁
• 如果很多人都在执行update操作，内部会把锁加上，不会造成数据混乱的问题
• 它内部一定会排着队，逐个执行的
• 但要执行 select，默认情况下，不会加锁，因为查询，拿过来加锁，没什么用
• 如果以后在特定的条件，就需要查询的时候人为申请一把锁也是可以的
• 它的使用规范是: 基于事务 + 特殊语法来实现

排它锁和共享锁

• mysql 按照类型分，可分为 排它锁 和 共享锁

• 先创建一张表

  create table `L1`(
    `id` int(11) not null auto_increment,
    `name` varchar(255) default null,
    `count` int(11) default null,
    primary key (`id`)
  ) engine=innodb default charset=utf8;
  

• 在这张表中操作 insert 很多数据，这里就不做操作了

1 ） 排它锁

• 主要语法 for update, 示例

  begin; -- 或 start transaction;
    select * from L1 where name='wang' for update; -- 注意，这里name不是索引，使用表锁
  commit; -- 事务结束，锁释放
  

  begin;
    select * from L1 where id=1 for update; -- 注意，这里id是索引，使用行锁
  commit;
  

• 如果事务不结束，其他人操作，都会受到阻塞，进行不下去

• 需要等到本人commit之后，才能结束锁，其他人才能继续

应用场景

• 抢购与库存减量
• update 操作默认加锁，如果当还剩最后一件，再执行就会出问题，会出现 -1 的问题
• 这样是不合适的

  update goods set count=count-1 where id=3
  

• 解决方案是使用排它锁，如下

sql操作示例

begin;
select count from goods where id=3 for update;
-- 获取个数进行判断
if 个数 > 0:
  update goods set count=count-1 where id=3;
else:
  -- 抢光了的处理
commit;

py操作

import pymysql
import threading

def task():
  # 建立连接
  conn = pymysql.connect(host='xxx.xxx.xxx.xxx', port=3306, user='root', password='xxxx', charset='utf8', db='userdb');
  # 限定结果格式，如下面的 fetchone 后，是这样的格式: { id: 1, age: 10 }
  # 如果是 fetchall, 则是这样的格式 ({ id: 1, age: 10 }, { id: 2, age: 11 })
  cursor = conn.cursor(pymysql.cursors.DictCursor)
  conn.begin() # 开启事务
  cursor.execute('select id, age from tran where id=2 for update') # 排它锁
  result = cursor.fetchone()
  current_age = result['age']
  if current_age > 0:
      cursor.execute('update tran set age==age-1 where id=2')
  else:
      print('已售完')
  conn.commit()
  cursor.close()
  conn.close()

def run():
  # 创建5个线程, 都去执行 task
  for i in range(5):
    t = threading.Thread(target=task)
    t.start()

if __name__ == '__main__':
  run()

2 ) 共享锁

主要语法是: lock in share mode

sql 用法示例

begin;
  select * from L1 where name='xxx' lock in share mode; -- name列不是索引，使用表锁
commit;

begin;
  select * from L1 where id=1 lock in share mode;    -- id列是索引 (行锁)
commit;

和排它锁的区别是: 加锁之后，其他可读，不可写

场景举例

• 目前有 A 和 B 两张锁
• 需要在 A 表中插入一条数据，插入前需要确保 B 表中相关的一条数据存在
• 但是，很可能在A插入的时候，B中的关键数据被删除
• 这时候就可以用 共享锁 防止B中相关数据被删除

sql 实现

begin;
  select * from B where id=1 lock in share mode;    -- id列是索引 (行锁)
  insert into A(name) values('wang');
commit;

• 一定要注意这一点：加锁之后，可以读，不可写(update, delete, insert)
• 实际上应用场景不多