序列号生成应使用哪种锁定方案和隔离级别? [英] Which locking scheme and isolation level should one use for sequence number generation?
问题描述
我想知道行业中用于生成序列号的一般惯例.
I would like to know the general practice used in the industry for generating sequence numbers.
即从表中获取最大值.递增并存储回去.
i.e. Get the max from a table. Increment it and store it back.
为了使其正常工作，应使用哪种隔离级别和/或锁定方案.
In order for this to work, which isolation level and/or locking scheme should be used.
我认为可序列化应该可以正常工作.但这只会阻止对表的更新.选择仍然可以完成.因此，将被更新的值可以相同.我们如何避免这种情况?
I thought serializable should work fine. But it only prevents updates to a table. Selection can still be done. So, the value that would be updated could be same. How can we avoid this?
谢谢！
推荐答案
您在交易范围内所做的任何事情都会受到比赛条件的限制.
因此，您执行的任何SQL查询都将获取最后使用的值，将其递增并存储在新行中，这意味着两个并发客户端可以获取相同的值并尝试使用它，从而导致键重复.
So any SQL query you do to get the last used value, increment it, and store it in a new row means that two concurrent clients could fetch the same value and try to use it, resulting in a duplicate key.
对此有一些解决方案:
-
锁定. 如果您使用
SELECT ... FOR UPDATE(如@Daniel Vassallo所述)，则每个客户端都会在读取的行上设置排他锁.
Locking. Each client sets an exclusive lock on the rows they read if you use
SELECT ... FOR UPDATE(as @Daniel Vassallo describes)
使用自动 .这种机制不保证竞争条件，因为新值的分配不考虑事务范围.好处是，没有两个并发客户端将获得相同的价值.但是，这意味着回滚并不会撤消值的分配. LAST_INSERT_ID() 函数会返回当前会话分配的最后一个自动增量值，即使其他并发客户端也在同一表或不同表中生成值.
Use auto-increment. This mechanism guarantees no race conditions, because allocation of new values happens without regard to transaction scope. As a benefit, no two concurrent clients will get the same value. This means, though, that a rollback doesn't undo allocation of a value. The LAST_INSERT_ID() function returns the last auto-increment value allocated by the current session, even if other concurrent clients are also generating values in the same table or different tables.
使用外部解决方案.不使用SQL而是使用应用程序中的某些其他系统来生成主键值.您有责任防范比赛条件.例如，您可以使用计数信号量.
Use an external solution. Generate primary key values not using SQL but with some other system in your application. You're responsible for protecting against race conditions. For instance you could use a counting semaphore.
使用伪随机的唯一ID.主键必须是唯一的，但不必是单调递增的整数.某些人使用 UUID() 函数生成随机保证几乎没有重复的128位数字.但是随后，您的主键必须使用更大的数据类型，例如CHAR(36)或BINARY(16)，并且编写临时查询很不方便.
Use a pseudorandom, unique id. Primary keys need to be unique, but they don't need to be monotonically increasing integers. Some people use the UUID() function to generate a random 128-bit number that's virtually guaranteed to not have duplicates. But then your primary keys have to use a larger data type such as CHAR(36) or BINARY(16) and it's inconvenient to write ad hoc queries.
SELECT * FROM MyTable WHERE id ='6ccd780c-baba-1026-9564-0040f4311e29';
SELECT * FROM MyTable WHERE id = '6ccd780c-baba-1026-9564-0040f4311e29';
您在评论中提到您阅读了一些有关使用自动增量的负面信息".当然，任何语言的任何功能都具有和不具有的功能.这并不意味着我们不应该使用这些功能，而是意味着我们应该学习如何正确使用它们.
You mention in a comment that you "read some negative things" about using auto-increment. Of course any feature in any language has do's and don'ts. It doesn't mean we shouldn't use those features -- it means we should learn how to use them properly.
您能描述您的担忧或关于自动增量的负面影响吗?也许与此线程相关的人可以解决他们.
Can you describe your concerns or any of the negative things about auto-increment? Perhaps folks on this thread can address them.
这篇关于序列号生成应使用哪种锁定方案和隔离级别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
