实例讲解MySQL中乐观锁和悲观锁

介绍

在多线程编程中，为了避免并发访问造成的数据不一致问题，一般使用锁来保证数据的一致性。MySQL中也提供了乐观锁和悲观锁两种机制，本文将详细讲解这两种锁的实现方式和使用场景。

悲观锁

悲观锁是一种在访问数据时悲观地认为其他线程可能会修改数据，因此对数据进行加锁处理，从而保证数据的一致性。通常情况下，悲观锁会在执行SQL语句时自动加锁，如下所示：

SELECT * FROM student WHERE id = 1 FOR UPDATE;

上面的SQL语句中使用了FOR UPDATE关键字，表示当前查询会涉及到数据修改，因此需要对查询结果加锁。

悲观锁的优缺点

悲观锁的优点是实现简单，易于理解和掌握。但是悲观锁会对性能造成一定的影响，因为悲观锁可能会导致多个线程之间互相等待，从而影响程序的并发性能。

悲观锁的示例

下面的示例演示了在MySQL中使用悲观锁实现多线程同时修改同一条数据的操作。

import threading
import time
import pymysql

def update_score(conn):
    cur = conn.cursor()
    # 使用悲观锁更新数据
    cur.execute("SELECT * FROM score WHERE id = 1 FOR UPDATE")
    score = cur.fetchone()[1]
    score += 10
    cur.execute("UPDATE score SET score = %s WHERE id = 1", (score,))
    conn.commit()
    cur.close()

conn = pymysql.connect(host='localhost', user='root', password='password', database='test', port=3306)
threads = []
for i in range(10):
    t = threading.Thread(target=update_score, args=(conn,))
    threads.append(t)

for t in threads:
    t.start()

for t in threads:
    t.join()

conn.close()

乐观锁

乐观锁是一种在访问数据时乐观地认为其他线程不会修改数据，因此不加锁直接执行操作。在操作完成前，如果发现数据已经被其他线程修改，则需要进行回滚操作。常用的乐观锁实现方式是通过增加一个版本号字段来实现，下面是一个示例：

CREATE TABLE goods (
  id INT(11) NOT NULL,
  name VARCHAR(255),
  price INT(11) NOT NULL DEFAULT '0',
  version INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这个示例中，goods表中增加了一个version字段，用于记录当前数据的版本号。在执行更新操作时，会先查询当前数据的版本号，然后将新的版本号更新到表中，如果查询到的版本号和更新前的版本号不一致，则说明数据已经被其他线程修改，需要回滚操作。

乐观锁的优缺点

乐观锁的优点是能够提高程序的并发性能，因为大多数情况下，数据访问不会涉及到冲突。但是乐观锁需要特殊的机制来处理并发访问，因此实现相对复杂。

乐观锁的示例

下面的示例演示了在MySQL中使用乐观锁实现多线程同时修改同一条数据的操作。

import threading
import time
import pymysql

def update_goods(conn):
    cur = conn.cursor()
    while True:
        try:
            # 开启事务
            conn.begin()
            # 查询当前的版本号
            cur.execute("SELECT version FROM goods WHERE id = 1")
            version = cur.fetchone()[0]
            # 将版本号加1并更新数据
            cur.execute("UPDATE goods SET price = price + 10, version = version + 1 WHERE id = 1 AND version = %s", (version,))
            # 提交事务
            conn.commit()
            break
        except pymysql.err.InternalError:
            conn.rollback()
            time.sleep(0.1)

conn = pymysql.connect(host='localhost', user='root', password='password', database='test', port=3306)
threads = []
for i in range(10):
    t = threading.Thread(target=update_goods, args=(conn,))
    threads.append(t)

for t in threads:
    t.start()

for t in threads:
    t.join()

conn.close()