在MySQL中模拟事务安全的SEQUENCE [英] Emulating a transaction-safe SEQUENCE in MySQL

问题描述

我们在InnoDB存储引擎和事务中大量使用MySQL，我们遇到了一个问题:我们需要一种很好的方法来在MySQL中模拟Oracle的SEQUENCE.要求是: -并发支持 -交易安全 -最高性能(意味着最大程度地减少锁和死锁)

We're using MySQL with InnoDB storage engine and transactions a lot, and we've run into a problem: we need a nice way to emulate Oracle's SEQUENCEs in MySQL. The requirements are: - concurrency support - transaction safety - max performance (meaning minimizing locks and deadlocks)

我们不在乎是否不使用某些值，即顺序上的间隔是可以的.有一个简单的方法来存档，即通过创建一个带有计数器的单独的InnoDB表，但这意味着它将参与事务并引入锁和等待.我正在考虑尝试使用带有手动锁，其他任何想法或最佳做法的MyISAM表吗?

We don't care if some of the values won't be used, i.e. gaps in sequence are ok. There is an easy way to archieve that by creating a separate InnoDB table with a counter, however this means it will take part in transaction and will introduce locks and waiting. I am thinking to try a MyISAM table with manual locks, any other ideas or best practices?

推荐答案

如果自动增量不足以满足您的需要，则可以使用 n 命名序列创建原子序列机制，如下所示:

If auto-increment isn't good enough for your needs, you can create a atomic sequence mechanism with n named sequences like this:

创建一个表来存储序列:

Create a table to store your sequences:

CREATE TABLE sequence (
  seq_name varchar(20) unique not null,
  seq_current unsigned int not null
);

假设您在表中有一行"foo"，您可以像这样原子地获取下一个序列ID:

Assuming you have a row for 'foo' in the table you can atomically get the next sequence id like this:

UPDATE sequence SET seq_current = (@next := seq_current + 1) WHERE seq_name = 'foo';
SELECT @next;

不需要锁.这两个语句都需要在同一会话中执行，以便在选择发生时实际定义局部变量@next.

No locks required. Both statements need to be executed in the same session, so that the local variable @next is actually defined when the select happens.

这篇关于在MySQL中模拟事务安全的SEQUENCE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！