SQL Server如何处理UPDATE事务? [英] How SQL Server handles UPDATE transactions?

问题描述

我们有一个用于生成唯一数字键的表.这些键然后用作其他表中的PrimaryKey.表结构是这样的:

We have a table which is used to Generate Unique Numeric keys. These keys are then used as a PrimaryKey in other tables. Table structure is like this:

TableName     VARCHAR
CurrentKey    INT

所以我们在此表中有数据，例如

So we have data in this table like

TableName   Customers
CurrentKey  400

因此，当我们需要表Customers的下一个主键时，我们从该表中获得CurrentKey，其中TableName是Customers，它将为我们提供400，我们将其递增(400 + 1)并对其进行更新还要在表中键入.所以我们的CurrentKey现在是401.

So when we need next primary key for table Customers we get the CurrentKey from this table where TableName is Customers, it will give us 400 we increment (400+1) it and we update this key in the table also. So our CurrentKey is 401 now.

我们用于此目的的sql是:

The sql we use for this purpose is:

SQL1:

DECLARE  @CurrentKey INT
UPDATE  myTable 
SET @CurrentKey = CurrentKey = CurrentKey + 1
WHERE   TableName = @TableName

我的问题是，我们是否需要锁定表，以便如果多个用户同时调用此键，则键可能不会重复?我确定SQL Server不允许重复的数据，但我不知道该如何...用表锁查询:

My question is that Do we need to LOCK the table so that the keys may not duplicate if multiple users call this at the same time? i am sure that SQL Server will not allow duplicate data but i don't know HOW... Query with Table Lock:

SQL2

BEGIN TRANSACTION
    DECLARE  @CurrentKey INT
    UPDATE  myTable WITH (TABLOCKX)
    SET @CurrentKey = CurrentKey = CurrentKey + 1
    WHERE   TableName = @TableName
END TRANSACTION

有人可以解释一下SQL Server如何处理UPDATE调用吗?

Can someone please explain how the SQL Server handles UPDATE calls?

推荐答案

每个SQL语句都在事务中运行，并且update语句始终使用锁来保护其更新. SQL Server不允许您读取半修改的行(对于大于8k的数据，有些例外.)

Each SQL statement runs in a transaction, and an update statement always uses a lock to protect its update. SQL Server does not allow you to read a half-modified row (with some exceptions for data that is larger than 8k.)

您的第一句话应该没问题.

Your first statement should be fine.

这篇关于SQL Server如何处理UPDATE事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！