sql server：这种嵌套在事务中是否足以从数据库获取唯一的编号？

发布于 2024-10-01 22:05:30 字数 921 浏览 1 评论 0原文

我想从表中生成一个唯一的编号。当然，它必须是线程安全的，所以当我检查最后一个数字并得到“3”，然后将“4”存储在数据库中时，我不希望其他人在这两个操作之间（获取数字）并将其存储在数据库中更高的位置）也可以取回“3”，然后也存储“4”

所以我想，将其放入这样的事务中：

begin transaction
        declare @maxNum int
        select @maxNum = MAX(SequenceNumber) from invoice
            where YEAR = @year
        if @maxNum is null
        begin
            set @maxNum = 0
        end
        set @maxNum = @maxNum + 1
        INSERT INTO [Invoice]
           ([Year]
           ,[SequenceNumber]
           ,[DateCreated])
     VALUES
           (@year
           ,@maxNum
           ,GETUTCDATE()
)

    commit transaction

    return @maxNum

但我想知道，将其放入事务中是否足够？我的第一个想法是：它会锁定此 sp 供其他人使用，但这是正确的吗？ sql server 如何知道第一步要锁定什么？

这种结构能否保证当我更新 @maxnum 值时没有其他人会执行 select @maxnum 部分，并且在那一刻收到相同的 @maxnum 正如我所做的那样，我遇到了麻烦。

我希望您了解我想要实现的目标，也希望您知道我是否确实选择了正确的解决方案。

编辑：也被描述为“如何单线程存储过程”

原文

i want to generate a unique number from a table.
It has to be thread safe of course, so when i check for the last number and get '3', and then store '4' in the database, i don't want anybody else just in between those two actions (get the number and store it one higher in the database) also to get '3' back, and then also storing '4'

So i thought, put it in a transaction like this:

begin transaction
        declare @maxNum int
        select @maxNum = MAX(SequenceNumber) from invoice
            where YEAR = @year
        if @maxNum is null
        begin
            set @maxNum = 0
        end
        set @maxNum = @maxNum + 1
        INSERT INTO [Invoice]
           ([Year]
           ,[SequenceNumber]
           ,[DateCreated])
     VALUES
           (@year
           ,@maxNum
           ,GETUTCDATE()
)

    commit transaction

    return @maxNum

But i wondered, is that enough, to put it in a transaction?
my first thought was: it locks this sp for usage by other people, but is that correct? how can sql server know what to lock at the first step?

Will this construction guarantee me that nobody else will do the select @maxnum part just when i am updating the @maxnum value, and at that moment receiving the same @maxnum as i did so i'm in trouble.

I hope you understand what i want to accomplish, and also if you know if i did choose the right solution.

EDIT:
also described as 'How to Single-Thread a stored procedure'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笔落惊风雨 2024-10-08 22:05:30

如果您想将年份和序列号存储在数据库中，并从中创建发票号，我会使用：

InvoiceYear 列（完全可以计算为 YEAR (InvoiceDate))
一个 InvoiceID INT IDENTITY 列，您可以每年将其重置为 1

创建一个计算列 InvoiceNumber 如下：

更改表 dbo.InvoiceTable
   ADD 发票编号 AS CAST(InvoiceYear AS VARCHAR(4)) + '-' +
           RIGHT('000000' + CAST(InvoiceID AS VARCHAR(6)), 6) 保留

这样，您就可以自动获取发票号码：

2010-000001
......
2010-001234
......
2010-123456

当然，如果您需要超过 6 位数字（= 100 万张发票） - 只需调整InvoiceID 列的 RIGHT() 和 CAST() 语句。

此外，由于这是一个持久计算列，因此您可以为其建立索引以进行快速检索。

这样：您不必担心并发、存储过程、事务和类似的事情 - SQL Server 会为您做这些 - 免费！

If you want to have the year and a sequence number stored in the database, and create an invoice number from that, I'd use:

a InvoiceYear column (which could totally be computed as YEAR(InvoiceDate))
an InvoiceID INT IDENTITY column which you could reset every year to 1

create a computed column InvoiceNumber as:

ALTER TABLE dbo.InvoiceTable
   ADD InvoiceNumber AS CAST(InvoiceYear AS VARCHAR(4)) + '-' +
           RIGHT('000000' + CAST(InvoiceID AS VARCHAR(6)), 6) PERSISTED

This way, you automagically get invoice numbers:

2010-000001
......
2010-001234
......
2010-123456

Of course, if you need more than 6 digits (= 1 million invoices) - just adjust the RIGHT() and CAST() statements for the InvoiceID column.

Also, since this is a persisted computed column, you can index it for fast retrieval.

This way: you don't have to worry about concurrency, stored procs, transactions and stuff like that - SQL Server will do that for you - for free!

回复收藏 0 原文

北城孤痞 2024-10-08 22:05:30

不，这还不够。 select 设置的共享锁不会阻止任何人同时读取相同的值。

更改为：

select @maxNum = MAX(SequenceNumber) from invoice where YEAR = @year

这样：

select @maxNum = MAX(SequenceNumber) from invoice with (updlock, holdlock) where YEAR = @year

这样您就可以用更新锁替换共享锁，并且两个更新锁彼此不兼容。
holdlock 表示锁定将一直保持到事务结束。所以你仍然需要交易位。

请注意，如果还有其他一些过程也想要进行更新，这将无济于事。如果其他过程在不提供 updlock 提示的情况下读取该值，它仍然能够读取计数器的先前值。这可能是一件好事，因为它可以提高其他读者不打算稍后进行更新的情况下的并发性，但它也可能不是您想要的，在这种情况下，要么更新所有过程以使用 updlock< /code>，或使用 xlock 来放置独占锁，与共享锁不兼容。

No, it's not enough. The shared lock set by the select will not prevent anyone from reading that same value at the same time.

Change this:

select @maxNum = MAX(SequenceNumber) from invoice where YEAR = @year

To this:

select @maxNum = MAX(SequenceNumber) from invoice with (updlock, holdlock) where YEAR = @year

This way you replace the shared lock with an update lock, and two update locks are not compatible with each over.
The holdlock means that the lock is to be held until the end of the transaction. So you do still need the transaction bit.

Note that this will not help if there's some other procedure that also wants to do the update. If that other procedure reads the value without providing the updlock hint, it will still be able to read the previous value of the counter. This may be a good thing, as it improves concurrency in scenarios where the other readers do not intend to make an update later, but it also may be not what you want, in which case either update all procedures to use updlock, or use xlock instead to place an exclusive lock, not compatible with shared locks.

回复收藏 0 原文

油饼 2024-10-08 22:05:30

事实证明，我不想锁定表，我只想一次执行一个存储过程。
在 C# 代码中，我会在另一个对象上放置一个锁，这就是这里讨论的内容
http://www.sqlservercentral.com/Forums/Topic357663-8-1.aspx

这就是我所使用的

declare @Result int
EXEC @Result =
sp_getapplock @Resource = 'holdit1', @LockMode = 'Exclusive', @LockTimeout = 10000 --Time to wait for the lock
IF @Result < 0
BEGIN
ROLLBACK TRAN
RAISERROR('Procedure Already Running for holdit1 - Concurrent execution is not supported.',16,9)
RETURN(-1)
END

，其中“holdit1”只是锁的名称。
@result 如果获取锁成功则返回0或1（其中一种是立即成功，另一种是在等待时获取锁）

As it turned out, i didn't want to lock the table, i just wanted to execute the stored procedure one at a time.
In C# code i would place a lock on another object, and that's what was discussed here
http://www.sqlservercentral.com/Forums/Topic357663-8-1.aspx

So that's what i used

declare @Result int
EXEC @Result =
sp_getapplock @Resource = 'holdit1', @LockMode = 'Exclusive', @LockTimeout = 10000 --Time to wait for the lock
IF @Result < 0
BEGIN
ROLLBACK TRAN
RAISERROR('Procedure Already Running for holdit1 - Concurrent execution is not supported.',16,9)
RETURN(-1)
END

where 'holdit1' is just a name for the lock.
@result returns 0 or 1 if it succeeds in getting the lock (one of them is when it immediately succeeds, and the other is when you get the lock while waiting)

回复收藏 0 原文

~没有更多了~