mysql 乐观锁语句

INSERT INTO user (id, name, version) VALUES (1, 'Alice', 1);

-- 查询用户信息
SELECT id, name, version FROM user WHERE id = 1;

-- 假设查询到的版本号为1
UPDATE user SET name = 'Bob', version = version + 1 WHERE id = 1 AND version = 1;

-- 假设使用Python进行重试
import time

max_retries = 3
retry_delay = 1  # 初始重试延迟时间（秒）

for attempt in range(max_retries):
    try:
        result = cursor.execute("""
            UPDATE user SET name = 'Bob', version = version + 1 WHERE id = 1 AND version = 1;
        """)
        if result.rowcount > 0:
            break
    except Exception as e:
        if attempt < max_retries - 1:
            time.sleep(retry_delay)
            retry_delay *= 2  # 指数退避
        else:
            raise e

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    version INT NOT NULL DEFAULT 1
);