SQL 常见锁
2022-02-20 16:05:25

本文基于pg数据库测试,mysql类似,但是也许有不同之处,以下示例必须在不同窗口进行,否则是一个事务得不到想要的结果

1. 数据库常见锁

我们在对数据库进行操作时常常会出现因为并发而导致数据不一致的问题,所以就有了相应的解决方案,那就是

锁机制又分为表锁行锁等等,我们常见的锁又分为悲观锁乐观锁,悲观锁又有共享锁排它锁等等

简单讲解下具体的意思和区别:

  • 表锁顾名思义就是会锁住整个表,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
  • 行锁那就是只会锁住一行数据了,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高
  • 悲观锁顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁
  • 乐观锁就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景,这样可以提高吞吐量
  • 共享锁(S锁)就是多个事务可以同时查询一个资源,但是都不能修改
  • 排它锁(X锁)就是禁止别人修改,只能自己修改

同一资源可以存在多个共享锁,但是只能存在一个排它锁

2. 排它锁示例

用法:在sql语句后面加FOR UPDATE即可,在pg中无论条件是主键还是非主键都是行锁,其他数据库可能非主键是表锁

1
2
3
SELECT *
FROM base.transfer
WHERE id = 8082531236099858432 FOR UPDATE;

image-20210624215845802

image-20210624215634303

image-20210624215739952

必须在事务中执行!!我们可以看到在结束事务前其他事务是无法进行修改的,待排它锁持有者完成事务后才会释放资源


补充一个跳过锁定资源的语句,加上SKIP LOCKED即可

1
2
3
SELECT *
FROM base.transfer
WHERE id = 8082531236099858432 FOR UPDATE SKIP LOCKED;

image-20210624220434788

image-20210624220450283

可以看到当第一个事务锁定资源后,第二个事务获取没有锁的资源的时候返回了0行,这样就能获取没有锁的资源

3. 共享锁