问题描述
有时我需要获取唯一 ID 并将其与记录一起存储，但我无法使用身份列.因此，我有一个表，它使用标签字段和整数提供唯一 ID.当需要一个唯一的 ID 时，我调用一个存储过程并传入标签，它会吐出与其关联的下一个 ID.当然，这在具有并发事务的环境中可靠很重要.也就是说，存储过程不应为给定的标签两次返回相同的值.我对事务隔离的有限理解导致我执行以下操作:
Sometimes I need to acquire a unique ID and store it with a record, but I am unable to use an identity column. So instead I have a table which provides unique IDs using a label field and an integer. When a unique ID is needed, I call a stored procedure and pass in the label, and it spits out the next ID associated with it.Of course it's important for this to be reliable in an environment with concurrent transactions. That is, the stored procedure should never return the same value twice for a given label. My limited understanding of transaction isolation has led me to do the following:
1) 将事务隔离级别设置为可序列化
1) Set transaction isolation level to serializable
2) SELECT id FROM UniqueIdTable WHERE label = @inputLabel
2) SELECT id FROM UniqueIdTable WHERE label = @inputLabel
3) UPDATE UniqueIdTable SET id = id + 1 WHERE label = @inputLabel
3) UPDATE UniqueIdTable SET id = id + 1 WHERE label = @inputLabel
4) 返回 2) 中检索到的 id
4) Return the id retrieved in 2)
但这真的安全吗?即使使用可序列化的隔离，两个线程是否仍然可以同时执行直到步骤 2)?我的理解是最高隔离级别仅保证单个事务将执行而不会遇到幻像行或更改来自其他线程的数据.如果是这种情况，对 GetID 函数的两个同时调用可能会返回相同的值.
But is this actually safe? Isn't it still possible for two threads to concurrently execute up to step 2), even with serializable isolation? It's my understanding that the highest isolation level only guarantees that a single transaction will execute without experiencing phantom rows or changing data from other threads. If this is the case, two simultaneous calls to the GetID function could return the same value.
我对隔离级别有什么误解吗?我如何保证不会发生这种情况?
Am I misunderstanding something about the isolation levels? How can I guarantee this won't occur?
我还有一个问题需要解决.假设我有一个表，其中有一个字段，其中包含第二个表的外键.最初第一个表中的记录在第二个表中没有相应的记录，因此我将 NULL 存储在该字段中.现在，在某个时刻，用户运行一个操作，该操作将在第二个表中生成一条记录，并将第一个表链接到它.这始终是一对一的关系，因此如果两个用户同时尝试生成记录，则会创建一条记录并链接到该记录，另一个用户会收到一条消息，说明该记录已存在.如何确保不会在并发环境中创建重复项?
I have another problem I need to sort out. Suppose I have a table with a field in it which holds foreign keys for a second table. Initially records in the first table do not have a corresponding record in the second, so I store NULL in that field. Now at some point a user runs an operation which will generate a record in the second table and have the first table link to it. This is always a one-to-one relationship, so if two users simultaneously try to generate the record, a single record is created and linked to, and the other user receives a message saying the record already exists.How do I ensure that duplicates are not created in a concurrent environment?
推荐答案
您可以使用 .
You could increment and fetch the ID in the update statement using output.
update UniqueIdTable
set ID = ID + 1
output deleted.ID
where label = @inputLabel
这篇关于处理 SQL Server 并发问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！