SQL server 选择锁粒度

转载

代码探险家 2024-10-14 18:13:36

文章标签 SQL server 选择锁粒度乐观锁的颗粒度数据悲观锁共享锁 文章分类 SQL Server 数据库

数据库的锁机制

数据库的锁从锁定的粒度上可以分为表级锁、行级锁和页级锁。MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如MyISAM和Memory存储引擎采用的是表级锁；BDB存储引擎采用的是页级锁，但也支持表级锁；InnoDB存储引擎既支持行级锁也支持表级锁，默认情况下采用行级锁。

表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页级锁：开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般。

无论是MyISAM还是InnoDB，无论锁粒度是表锁还是行锁，都会涉及读写的问题，从模式上就会有共享锁（读锁）和排他锁（写锁），下面我们来重点阐述。

共享锁和排他锁（读锁和写锁）

共享锁又称为读锁，是读操作创建的锁，当数据被某个读操作加上共享锁后，其他请求也可以再次对该数据增加共享锁，其他用户可以并发的读数据，但是不能对数据进行修改（即不能给数据增加排他锁），要修改数据需要等待共享锁释放。

排他锁（互斥锁）又称为写锁，是写操作创建的锁，一旦数据被加上了排他锁，其他请求就无法再为数据加任何类型的锁（读锁和写锁都不能加），直到上一个请求释放了对数据的锁，下一个请求才有可能加锁成功。

MyISAM的锁机制

MyISAM只支持表锁，在执行查询操作前，会自动给涉及的表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预。如果用户想显示的加锁可以使用如下命令：

锁定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]
解锁表：UNLOCK tbl_name

在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁。在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因。

对表test_table增加读锁：

LOCK TABLES test_table READ
UNLOCK test_table

对表test_table增加写锁：

LOCK TABLES test_table WRITE
UNLOCK test_table

当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且同一个表在SQL语句中出现多少次，就要通过SQL语句中不同的别名锁定多少次，否则也会出错，例如如下SQL语句：

SELECT a.first_name,b.first_name FROM actor a,actor b WHERE a.first_name = b.first_name;

该SQL语句中，actor表以别名的方式出现了两次，分别是a和b，这时如果要在该SQL执行之前加锁就要使用以下SQL：

LOCK TABLES actor AS a READ,actor AS b READ;

我们来看一下MyISAM锁调度的问题？

MyISAM引擎的读锁和写锁是互斥的，读写操作是串行的。那么一个进程请求某个表的读锁，同时另一个进程也请求同一个表的写锁，MySQL会如何处理呢？

答案是写进程先获得锁。

不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因。因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞，这种情况有时可能会变得非常糟糕。

幸好我们可以通过一些设置来调节MyISAM的调度行为：

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

另外MySQL也供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的写锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程获得锁的机会。

InnoDB的锁机制

如果事务T对数据A加上共享锁后，其他事务只能对A再加共享锁，不能加排他锁。获取共享锁的事务只能读数据，不能修改数据。

如果事务T对数据A加上排他锁后，其他事务不能再对A加任何类型的锁。获取排他锁的事务既能读数据，又能修改数据。

对于更新操作(INSERT、UPDATE、DELETE等)，InnoDB会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB不会加任何锁。事务可以通过以下语句显示给SELECT操作加共享锁和排他锁。

共享锁：SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，MySQL会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁：SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，MySQL会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

悲观锁和乐观锁

在并发的环境中，会存在多个用户同时更新同一条数据，这时就可能产生冲突。冲突的结果：

更新丢失：一个事务的更新覆盖了其他事务的更新结果，这就是更新丢失。
脏读：一个事务读取了其他完成一半的事务的记录时，就会发生脏读取。

为了解决上述问题，引入了并发控制机制。

乐观锁(乐观并发控制)和悲观锁(悲观并发控制)是并发控制的主要手段，其实不仅关系型数据库中有乐观锁和悲观锁的概念，像redis，memcached等都有类似的概念。所以不要把乐观锁和悲观锁狭隘的理解为DBMS中的概念，更不能把他们和数据库中提供的锁机制混为一谈。其实，在DBMS中悲观锁正是利用了数据库提供的锁机制实现的。根据不同的业务场景，应该选择不同的并发控制方式。

悲观锁

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

如果一个事务执行的操作在某些数据上应用了悲观锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

数据库中悲观锁的流程如下：

对任意记录进行修改前，先尝试为该记录加上排他锁。如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。如果成功加锁，那么就可以对记录做修改，事务完成后就会释放锁了。其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

使用场景举例（以MySQL的InnoDB为例）

商品goods表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单，那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。

如果不采用锁，那么操作方法如下：

//1.查询出商品信息
select status from t_goods where id=1;
//2.根据商品信息生成订单
insert into t_orders (goods_id) values (1);
//3.修改商品status为2
update t_goods set status=2 where id=1;

上面这种场景在高并发访问的情况下很可能会出现问题。前面已经提到，只有当goods status为1时才能对该商品下单，上面第一步操作中，查询出来的商品status为1。但是当我们执行第三步update操作的时候，有可能出现其他人先一步对商品下单把goods status修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个商品被下单2次，使得数据不一致。所以说这种方式是不安全的。

如何解决这个问题呢？

我们可以使用悲观锁来解决这个问题，原理就是当我们在查询出goods信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为goods相关数据被锁定了，就不会出现有其他事务来对其进行修改了。要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

我们可以使用命令关闭MySQL的autocommit模式：

set autocommit=0;

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

//1.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//2.查询出商品信息
select status from t_goods where id=1 for update;
//3.根据商品信息生成订单
insert into t_orders (goods_id) values (1);
//4.修改商品status为2
update t_goods set status=2 where id=1;
//5.提交事务
commit;/commit work;

通过for update实现悲观锁，上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交。

使用锁时我们需要注意一些锁的级别，MySQL InnoDB默认为行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。悲观并发控制实际上是"先加锁再访问"的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还会降低并行性。

乐观锁

相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

实现数据版本有两种方式：

第一种是使用版本号version
第二种是使用时间戳timestamp

数据版本，为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本标识与第一次取出来的版本标识进行比对，如果数据库表当前版本标识与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

DBMS会假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据，如果其他事务有更新的话，正在提交的事务会进行回滚。

使用场景举例（以MySQL的InnoDB为例）

还是拿之前的例子：商品goods表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单，那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。

下单操作包括3步骤（该操作为一个事务）：

//1.查询出商品信息
select status,version from t_goods where id=1;
//2.根据商品信息生成订单
insert into t_orders (goods_id) values (1);
//3.修改商品status为2
update t_goods
set status=2,version=version+1 where id=1 and version=#{version};