这个存储过程是线程安全的吗?(或 SQL Server 上的任何等效项) [英] Is this stored procedure thread-safe? (or whatever the equiv is on SQL Server)

问题描述

在 SO 上的其他人的帮助下，我今天早上创建了几个表和存储过程，因为我离 DB 程序员还很远.

With the help of others on SO I've knocked up a couple of Tables and Stored Procedures, this morning, as I'm far from a DB programmer.

有人介意看看这个并告诉我它是否是线程安全的吗?我想这可能不是 DBA/DB 开发人员使用的术语，但我希望你能明白:基本上，如果这个 sp 正在执行并且另一个 sp 同时出现会发生什么?一个可以干扰另一个吗?这甚至是 SQL/SP 中的问题吗?

Would someone mind casting an eye over this and telling me if it's thread-safe? I guess that's probably not the term DBAs/DB developers use but I hope you get the idea: basically, what happens if this sp is executing and another comes along at the same time? Could one interfere with the other? Is this even an issue in SQL/SPs?

CREATE PROCEDURE [dbo].[usp_NewTicketNumber]
    @ticketNumber int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [TEST_Db42].[dbo].[TicketNumber]
               ([CreatedDateTime], [CreatedBy])
         VALUES
                (GETDATE(), SUSER_SNAME())
    SELECT @ticketNumber = IDENT_CURRENT('[dbo].[TicketNumber]');
    RETURN 0;
END

推荐答案

您可能不想使用 IDENT_CURRENT - 这将返回在任何会话和任何范围内在相关表上生成的最新标识.如果其他人在错误的时间进行了插入操作，您将获得他们的 ID！

You probably do not want to be using IDENT_CURRENT - this returns the latest identity generated on the table in question, in any session and any scope. If someone else does an insert at the wrong time you will get their id instead!

如果您想获得由您刚刚执行的插入生成的标识，那么最好使用 OUTPUT 子句来检索它.过去通常为此使用 SCOPE_IDENTITY()，但在并行执行计划下存在问题.

If you want to get the identity generated by the insert that you just performed then it is best to use the OUTPUT clause to retrieve it. It used to be usual to use the SCOPE_IDENTITY() for this but there are problems with that under parallel execution plans.

线程安全的主要 SQL 等效项是执行导致意外或不良行为的多个语句.我能想到的这种行为的两种主要类型是锁定(特别是死锁)和并发问题.

The main SQL equivalent of thread safety is when multiple statements are executed that cause unexpected or undesirable behaviour. The two main types of such behaviour I can think of are locking (in particular deadlocks) and concurrency issues.

当一个语句阻止其他语句访问它正在处理的行时，就会出现锁定问题.这可能会影响性能，在最坏的情况下，两个语句进行了无法协调的更改并发生死锁，导致一个语句被终止.

Locking problems occur when a statement stops other statements from accessing the rows it is working with. This can affect performance and in the worst scenario two statements make changes that cannot be reconciled and a deadlock occurs, causing one statement to be terminated.

但是，除非涉及其他事情(例如数据库事务)，否则像您这样的简单插入不应导致锁定.

However, a simple insert like the one you have should not cause locks unless something else is involved (like database transactions).

并发问题(对它们的描述非常糟糕)是由对数据库记录的一组更改覆盖对相同记录的其他更改引起的.同样，插入记录时这应该不是问题.

Concurrency issues (describing them very poorly) are caused by one set of changes to database records overwriting other changes to the same records. Again, this should not be a problem when inserting a record.

这篇关于这个存储过程是线程安全的吗?(或 SQL Server 上的任何等效项)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！