由于延迟，避免在SQL server中复制

I have a POS like system in C#, and for long time it not present any problem (it was just one POS). But in this days are 4 POS using the system, and connected to the same database and all the sales of one POS go to the same Audit (table) where all of the others sales go. So in this system this is the procedure

我在c#中有一个类似POS的系统，很长一段时间都没有出现任何问题(它只是一个POS)。但是现在有4个POS在使用这个系统，并且连接到同一个数据库，一个POS的所有销售都进入了相同的审计(表)，而其他所有的销售都去了这个审计(表)。在这个系统中，这是程序

Function to get the last Ticket number (with simple SELECT)
函数获取最后的票号(使用简单的SELECT)
Add 1 to that number (next tickt no).
再加1(下一项是no)。
Generates a ID Code injecting this Ticket number (with the terminal, date, and employee code) into the algorithm
生成将此票号(带有终端、日期和员工代码)注入算法的ID代码
Insert record of the sale into database with all the necesary information (Date, Client, Employee, IDCode, etc.) (with simple INSERT INTO)
将销售记录插入到数据库中，包含所有必要的信息(日期、客户、员工、IDCode等)(只需简单地插入)

But having 4 POS I realize that some sales where having the same Ticket number, fortunately the Ticket ID code are not the same because the terminal and the employee are different, but how can avoid this?

但是有了4个POS，我意识到有些销售有相同的票号，幸运的是由于终端和员工不同，票号不一样，但是怎么能避免呢?

Edit 1: Every POS system have dual function, in one mode the POS sales are centralized, and every POS in this mode generates consecutive tickets (like they all where one POS), in the other mode every POS have their own Ticket numertion, for that reason I can't use the identity.

编辑1:每个POS系统都有双功能，在一种模式下POS销售是集中的，这个模式下的每个POS都会产生连续的球票(就像每个POS都有一个球票一样)，而在另一种模式下，每个POS都有自己的球票编号，所以我不能使用这个标识。

4 个解决方案

#1

Just use a sequence to generate the next ticket number.

只需使用一个序列生成下一个票号。

CREATE SEQUENCE Tickets
    START WITH 1
    INCREMENT BY 1;

Then each POS just do

然后每个po都这样做

SELECT NEXT VALUE FOR Tickets;

The sequence is guaranteed to never return the same number twice.

保证序列不会返回相同的数字两次。

#2

As has been mentioned, if the TicketNumber is sequential and unique, it sounds like an IDENTITY field would be the way to go. BUT, if for some reason there is something preventing that, or if that requires too many changes as this time, you could constrain the process itself to be single-threaded by creating a lock on the ID Code generation process itself through the use of Application Locks (see sp_getapplock and sp_releaseapplock). Application Locks let you create locks around arbitrary concepts. Meaning, you can define the @Resource as "generate_id_code" which will force each caller to wait their turn. It would follow this structure:

如前所述，如果TicketNumber是连续的和惟一的，那么似乎应该使用标识字段。但是，如果由于某种原因出现了某些问题，或者这一次需要进行太多更改，那么可以通过使用应用程序锁在ID代码生成过程本身上创建一个锁来将进程本身约束为单线程的(参见sp_getapplock和sp_releaseapplock)。应用程序锁允许您围绕任意概念创建锁。也就是说，您可以将@Resource定义为“generate_id_code”，它将强制每个调用者等待他们的转换。它将遵循以下结构:

    BEGIN TRANSACTION;
    EXEC sp_getapplock @Resource = 'generate_id_code', @LockMode = 'Exclusive';

    ...current 4 steps to generate the ID Code...

    EXEC sp_releaseapplock @Resource = 'generate_id_code';
    COMMIT TRANSACTION;

You need to manage errors / ROLLBACK yourself (as stated in the linked MSDN documentation) so put in the usual TRY / CATCH. But, this does allow you to manage the situation.

您需要自己管理错误/回滚(如链接的MSDN文档中所述)，因此请放入通常的TRY / CATCH。但是，这确实允许你管理这种情况。

Please note: sp_getapplock / sp_releaseapplock should be used sparingly; Application Locks can definitely be very handy (such as in cases like this one) but they should only be used when absolutely necessary.

请注意:sp_getapplock / sp_releaseapplock应谨慎使用;应用程序锁绝对是非常方便的(例如在这种情况下)，但是它们只在绝对必要时才使用。

#3

You need to do this in an atomic action. So you can wrap everything in a transaction and lock the table. See here for a good discussion on locking etc.

您需要在原子操作中进行此操作。因此，您可以将所有内容打包到事务中并锁定表。关于锁定等问题，请参看这里。

Locking will slow down everything else since everything will start waiting for the table to free up for it to complete and that may not be something you can live with.

锁会减慢其他一切，因为一切都将开始等待表释放，等待它完成，而这可能不是您可以忍受的事情。

Or you ~~can~~ should use an identity on the column which will be managed by the database and maintain unique incrementing numbers.

或者，您可以在列上使用由数据库管理的标识，并维护惟一的递增编号。

You could also create your primary key (hope you have one) to be a combination of a few things. And then you could keeping a running number for each POS endpoint to see more data about how they are performing. But that gets more into analytics which isn't in scope here.

您还可以将主键(希望您有一个)创建为一些东西的组合。然后，您可以为每个POS端点保留一个运行号，以查看关于它们如何执行的更多数据。但这更多地涉及到分析，而这里没有。

#4

I would strongly suggest moving away from the current approach if you can and changing to a GUID PK.

如果可以的话，我强烈建议您不要使用当前的方法，改用GUID PK。

However, I realize that in some cases a redesign is not possible (we have the exact same scenario that you describe in a legacy database).

然而，我意识到在某些情况下，重新设计是不可能的(我们的场景与您在遗留数据库中描述的完全相同)。

In this case, you can get the maximum value safely using the UPDLOCK table hint in combination with the insert command and use the OUTPUT INSERTED functionality to retrieve the new primary key value into a local variable if needed:

在这种情况下，您可以使用UPDLOCK表提示与insert命令结合，安全地获取最大值，并使用插入的输出功能在需要时将新的主键值检索到本地变量中:

DECLARE 
  @PK Table (PK INT NOT NULL) 

INSERT 
  INTO Audit (
       TicketNumber, 
       Terminal,
       Date,
       EmployeeCode,
       Client,
       IDCode,
       ... other fields )
  /* Record the new PK in the tablevariable */
OUTPUT INSERTED.TicketNumber INTO @PK
SELECT IsNull(MAX(TicketNumber), 0) + 1, 
       @Terminal,
       @Date,
       @EmployeeCode,
       @Client,
       @IDCode,
       ... other values
  FROM Audit WITH (UPDLOCK)

DECLARE @TicketNumber INT

  /* Move the new PK from the local tablevariable into a local variable for subsequent use */
SELECT @TicketNumber = PK
  FROM @PK

#1