1
-- 查看当前自增锁模式
2
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
3
-- value: 2 (交叉模式)

1
-- 事务 A
2
BEGIN;
3
INSERT INTO users (name) VALUES ('Alice');  -- id = 1
4
ROLLBACK;  -- 回滚，id=1 被释放
5

6
-- 事务 B（独立事务）
7
INSERT INTO users (name) VALUES ('Bob');  -- id = 2

1
SELECT * FROM users;
2
+----+-------+
3
| id | name  |
4
+----+-------+
5
|  2 | Bob   |  -- id=1 被回滚，id 不连续
6
+----+-------+

1
-- 批量插入多条
2
INSERT INTO users (name) VALUES
3
    ('A'), ('B'), ('C'), ('D'), ('E');
4

5
SELECT * FROM users;
6
+----+-------+
7
| id | name  |
8
+----+-------+
9
|  1 | A     |
10
|  2 | B     |
11
|  3 | C     |
12
|  4 | D     |
13
|  5 | E     |
14
+----+-------+

1
-- 部分插入失败
2
INSERT IGNORE INTO users (id, name) VALUES
3
    (1, 'A'),  -- 重复 id，失败
4
    (6, 'B');  -- 只有这条成功
5

6
SELECT * FROM users;
7
+----+-------+
8
| id | name  |
9
+----+-------+
10
|  1 | A     |
11
|  6 | B     |  -- id=2,3,4,5 被跳过
12
+----+-------+

1
-- 事务 A
2
BEGIN;
3
INSERT INTO users (name) VALUES ('Alice');  -- 分配 id=1
4
-- 事务 A 还没有提交
5

6
-- 事务 B（在另一个连接）
7
BEGIN;
8
INSERT INTO users (name) VALUES ('Bob');  -- 分配 id=2
9
COMMIT;
10

11
-- 事务 A
12
COMMIT;  -- 提交

1
SELECT * FROM users;
2
+----+-------+
3
| id | name  |
4
+----+-------+
5
|  2 | Bob   |  -- Bob 先提交，id=2 先可见
6
|  1 | Alice |  -- Alice 后提交，id=1 后可见
7
+----+-------+

1
-- innodb_autoinc_lock_mode = 2 (交叉模式)
2
-- 自增值在语句开始前就确定了

1
# 伪代码：自增 ID 分配
2
def allocate_ids(count):
3
    # 先获取当前计数器值
4
    current = innodb_counter.get()
5
    # 增加到 current + count
6
    innodb_counter.set(current + count)
7
    # 返回当前值到 current + count - 1
8
    return range(current, current + count)

1
-- 创建序列表
2
CREATE TABLE sequence (
3
    name VARCHAR(50) PRIMARY KEY,
4
    current_value BIGINT NOT NULL
5
);
6

7
-- 获取下一个值（事务安全）
8
CREATE FUNCTION next_val(seq_name VARCHAR(50))
9
RETURNS BIGINT
10
BEGIN
11
    UPDATE sequence
12
    SET current_value = current_value + 1
13
    WHERE name = seq_name;
14
    RETURN (SELECT current_value FROM sequence WHERE name = seq_name);
15
END;

1
# 雪花算法实现
2
class Snowflake:
3
    def __init__(self, worker_id):
4
        self.worker_id = worker_id
5
        self.sequence = 0
6
        self.last_timestamp = -1
7

8
    def generate(self):
9
        # 保证单调递增
10
        timestamp = int(time.time() * 1000)
11
        if timestamp == self.last_timestamp:
12
            self.sequence = (self.sequence + 1) & 4095
13
        else:
14
            self.sequence = 0
15
        self.last_timestamp = timestamp
16
        return ((timestamp - 1288834974657) << 22) | \
17
               (self.worker_id << 12) | self.sequence