MySQL悲观锁与乐观锁

发表于

悲观锁与乐观锁是两种常见的资源并发锁设计思路。

使用场景

在电商系统中，有一张product表，里面包含有商品的id和商品的总数，在高并发场景，如秒杀。每次用户秒杀成功，则商品的总数的减1，如果对商品的总数的记录不加锁，则有可能出现数据不一致的情况：

1
2
3
4
5
6
7
create table product (
    `id` int not null COMMENT '自增ID',
    `product_id` int not null COMMENT '商品ID',
    `total` int not null default 0 COMMENT '库存数量',
    unique key `idx_pid` (`product_id`),
    primary key (`id`)
) engine=innodb default charset=utf8;

如下面的代码，在并发环境下，可能会出现问题：

1
2
3
4
5
6
7
8
9
10
11
12
13
def update_product(product_id):
    try:
        cursor.execute('SELECT * FROM product WHERE product_id = %s', product_id)
        result = cursor.fetchone()
        if result:
            product = Product(**result)
            if product.total > 0:
                row = cursor.execute('UPDATE product SET total = product.total - 1 WHERE product_id = %s', product_id)
                db.commit()   
    except:
        db.rollback()
    finally:
        db.close()

悲观锁

悲观锁的特点是先获取锁，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。在数据库上的悲观锁需要数据库本身提供支持，即通过常用的排他锁操作来实现悲观锁。

排他锁

对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。

语法为：

1
SELECT * FROM table FOR UPDATE

表中有如下数据：

1
2
3
4
5
6
7
8
mysql> SELECT * FROM product;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
|  1 |      90047 |    99 |
+----+------------+-------+
2 rows in set (0.01 sec)

现在对表中product_id为90046的记录进行锁定：

1
2
3
4
5
6
7
8
9
10
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM product WHERE product_id = 90046 FOR UPDATE;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
+----+------------+-------+
1 row in set (0.00 sec)

上面的操作开启了一个事务，并且锁定product_id为90046的记录，锁只有在执行COMMIT或者ROLLBACK的时候才会释放。现在另一个用户如果对product_id为90046的记录加锁，则会被阻塞，如果长时间获得不了锁，则会返回以下错误：

1
2
mysql> SELECT * FROM product WHERE product_id = 90046 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
共享锁

对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。

语法为：

1
SELECT * FROM table LOCK INT SHARE MODE

当用户锁定product_id为90046的记录，如果使用排他锁再对相同的记录进行锁定时，会被阻塞，读取会失败。如果使用共享锁，则可以读取该记录：

用共享锁锁定记录：

1
2
3
4
5
6
7
mysql> SELECT * FROM product WHERE product_id = 90046 LOCK IN SHARE MODE;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
+----+------------+-------+
1 row in set (0.00 sec)

另一个用户执行相同的操作，读取记录成功：

1
2
3
4
5
6
7
mysql> SELECT * FROM product WHERE product_id = 90046 LOCK IN SHARE MODE;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
+----+------------+-------+
1 row in set (0.01 sec)
行锁和表锁

根据锁的级别可以分为行锁和表锁。行锁表示锁住当前行记录，对其他记录没有影响。表锁表示锁住整张表，只有当所释放后，才能对其他记录进行操作。

MyISAM引擎操作数据都是使用的表锁，InnoDB引擎可以使用行级锁。

在Mysql中，InnoDB引擎行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种。如果不通过索引条件检索数据或者索引条件不明确，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。

行锁

上面的product_id在创建表使用的唯一索引的字段，而且使用的是InnoDB引擎。当锁定product_id为90046的记录，只锁定当前行，其他的行则不受影响：

锁定90046的记录：

1
2
3
4
5
6
7
mysql> SELECT * FROM product WHERE product_id = 90046 FOR UPDATE;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
+----+------------+-------+
1 row in set (0.00 sec)

另一个用户更新其他记录，则不受影响：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> UPDATE product SET total = 90 WHERE product_id = 90047;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM product;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
|  1 |      90047 |    90 |
+----+------------+-------+
2 rows in set (0.00 sec)

当锁定主键id时，使用的也是行锁，不影响其他的记录：

1
2
3
4
5
6
7
mysql> SELECT * FROM product WHERE id = 0 FOR UPDATE;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
+----+------------+-------+
1 row in set (0.00 sec)

另一个用户更新其他行的记录则不受影响：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> UPDATE product SET total = 80 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM product;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
|  1 |      90047 |    80 |
+----+------------+-------+
2 rows in set (0.00 sec)
表锁

如果不通过主键和索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。

如对total字段进行加锁，则会把整张表锁住，导致其他行无法更新：

1
2
3
4
5
6
7
mysql> SELECT * FROM product WHERE total = 10 FOR UPDATE;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
+----+------------+-------+
1 row in set (0.00 sec)

另一个用户更新其他行则将会受影响：

1
2
3
mysql> UPDATE product SET total = 70 WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

如果主键或索引不明确，也会导致整张表被锁住：

1
2
3
4
5
6
7
mysql> SELECT * FROM product WHERE product_id != 90047 FOR UPDATE;
+----+------------+-------+
| id | product_id | total |
+----+------------+-------+
|  0 |      90046 |    10 |
+----+------------+-------+
1 row in set (0.00 sec)

另一个用户更新其他行则将会受影响：

1
2
mysql> UPDATE product SET total = 90 WHERE product_id = 90047;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

乐观锁

每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

乐观锁一般来说有以下2种方式：：

  1. 在数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

  2. 在数据库表增加一个时间戳（timestamp）,在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。

在上面的操作，用乐观锁实现。代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
try:
    cursor.execute('SELECT * FROM product WHERE product_id = %s', product_id)
    result = cursor.fetchone()
    if result:
        product = Product(**result)
        if product.total > 0:
            row = cursor.execute('UPDATE product SET total = product.total - 1, version = product.version + 1 WHERE product_id = %s AND version = %s', %(product_id, version))
            if row != 1:
                print('update fail...')
except:
    pass
finally:
    db.close()

总结

悲观锁： 比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都会进行加锁，这样会增加大量的锁的开销，降低了系统的吞吐量。

乐观锁： 比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。

对于在分布式系统中，需要使用锁，可以考虑使用Redis或Zookeeper。