SQL 有没有办法同时 SELECT 和 UPDATE 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/497464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to SELECT and UPDATE rows at the same time?
提问by DavGarcia
I'd like to update a set of rows based on a simple criteria and get the list of PKs that were changed. I thought I could just do something like this but am worried about possible concurrency problems:
我想根据一个简单的条件更新一组行并获取已更改的 PK 列表。我以为我可以做这样的事情，但我担心可能出现的并发问题：
SELECT Id FROM Table1 WHERE AlertDate IS NULL;
UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL;
If that is wrapped in a transaction are there any concurrency issues that can occur? Or is there a better way to do this?
如果它包含在事务中，是否会发生任何并发问题？或者有没有更好的方法来做到这一点？
回答by Mark Canlas
Consider looking at the OUTPUT clause:
考虑查看OUTPUT 子句：
USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
回答by K. R.
Many years later...
许多年后...
The accepted answer of using the OUTPUT clause is good. I had to dig up the actual syntax, so here it is:
使用 OUTPUT 子句的公认答案是好的。我不得不挖掘实际的语法，所以这里是：
DECLARE @UpdatedIDs table (ID int)
UPDATE
Table1
SET
AlertDate = getutcdate()
OUTPUT
inserted.Id
INTO
@UpdatedIDs
WHERE
AlertDate IS NULL;
ADDEDSEP 14, 2015:
ADDED2015年9月14日：
"Can I use a scalar variable instead of a table variable?" one may ask... Sorry, but no you can't. You'll have to SELECT @SomeID = ID from @UpdatedIDsif you need a single ID.
“我可以使用标量变量而不是表变量吗？” 有人可能会问......对不起，但不，你不能。SELECT @SomeID = ID from @UpdatedIDs如果您需要一个 ID ，则必须这样做。
回答by Bill Karwin
One way to handle this is to do it in a transaction, and make your SELECT query take an update lock on the rows selected until the transaction completes.
处理此问题的一种方法是在事务中执行此操作，并使您的 SELECT 查询对所选行进行更新锁定，直到事务完成。
BEGIN TRAN
SELECT Id FROM Table1 WITH (UPDLOCK)
WHERE AlertDate IS NULL;
UPDATE Table1 SET AlertDate = getutcdate()
WHERE AlertDate IS NULL;
COMMIT TRAN
This eliminates the possibility that a concurrent client updates the rows selected in the moment between your SELECT and your UPDATE.
这消除了并发客户端更新在 SELECT 和 UPDATE 之间选择的行的可能性。
When you commit the transaction, the update locks will be released.
当您提交事务时，更新锁将被释放。
Another way to handle this is to declare a cursor for your SELECT with the FOR UPDATE option. Then UPDATE WHERE CURRENT OF CURSOR. The following is not tested, but should give you the basic idea:
处理此问题的另一种方法是使用 FOR UPDATE 选项为 SELECT 声明一个游标。然后更新光标的位置。以下内容未经测试，但应该给你基本的想法：
DECLARE cur1 CURSOR FOR
SELECT AlertDate FROM Table1
WHERE AlertDate IS NULL
FOR UPDATE;
DECLARE @UpdateTime DATETIME
SET @UpdateTime = GETUTCDATE()
OPEN cur1;
FETCH NEXT FROM cur1;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Table1 AlertDate = @UpdateTime
WHERE CURRENT OF cur1;
FETCH NEXT FROM cur1;
END
回答by Kevin Fairchild
回答by Gordon Bell
Perhaps something more like this?
也许更像这样？
declare @UpdateTime datetime
set @UpdateTime = getutcdate()
update Table1 set AlertDate = @UpdateTime where AlertDate is null
select ID from Table1 where AlertDate = @UpdateTime
回答by Kanagavelu Sugumar
I have faced the same issue; I have to update the credit amount, and have to get modified time, along with credit details from DB. It is basically
我也遇到过同样的问题；我必须更新信用金额，并且必须获得修改时间以及来自 DB 的信用详细信息。它基本上是
SYNCHRONOUSLY/ATOMICALLY perform (UPDATE then GET) in MYSQL
在 MYSQL 中同步/原子地执行（更新然后获取）
I have tried many options and found one that solved my issue.
我尝试了很多选择，并找到了一个解决了我的问题。
1) OPTION_1 SELECT FOR UPDATE
1) OPTION_1 选择更新
This is maintaining the lock till update (SYNC from GET to UPDATE), but i need lock after update till the GET.
这是保持锁定直到更新（从 GET 同步到更新），但我需要在更新后锁定直到 GET。
2) OPTION_2 Stored procedure
2) OPTION_2 存储过程
Stored procedure will not execute synchronously like redis lua, So there also we need sync code to perform that.
存储过程不会像 redis lua 那样同步执行，所以我们也需要同步代码来执行。
3) OPTION_3 Transaction
3) OPTION_3 交易
I have used JPA entityManager like below, thought that before commit no one can update, and before commit i will get the updated object along with modified time (from DB). But i didn't get the latest object. Only commit i got the latest.
我使用过如下所示的 JPA entityManager，认为在提交之前没有人可以更新，并且在提交之前我将获得更新的对象以及修改时间（来自 DB）。但我没有得到最新的对象。只提交我得到最新的。
try {
entityManager.getTransaction().begin();
//entityManager.persist(object);
int upsert = entityManager.createNativeQuery(
"update com.bill.Credit c set c.balance = c.balance - ?1
where c.accountId = ?2 and c.balance >= ?1").executeUpdate();
//c.balance >= ? for limit check
Credit newCredit = entityManager.find(Credit.class, "id");
entityManager.refresh(newCredit); //SHOULD GET LATEST BUT NOT
entityManager.getTransaction().commit();
} finally {
entityManager.unwrap(Session.class).close();
}
4) OPTION_4 LOCK solved the issue, so before update i acquired the lock; then after GET i have released the lock.
4) OPTION_4 LOCK 解决了这个问题，所以在更新之前我获得了锁；然后在GET之后我释放了锁。
private Object getLock(final EntityManager entityManager, final String Id){
entityManager.getTransaction().begin();
Object obj_acquire = entityManager.createNativeQuery("SELECT GET_LOCK('" + Id + "', 10)").getSingleResult();
entityManager.getTransaction().commit();
return obj_acquire;
}
private Object releaseLock(final EntityManager entityManager, final String Id){
entityManager.getTransaction().begin();
Object obj_release = entityManager.createNativeQuery("SELECT RELEASE_LOCK('" + Id + "')").getSingleResult();
entityManager.getTransaction().commit();
return obj_release;
}
回答by Kanagavelu Sugumar
Edit: my bad, you wanted the select to show results after the update, not update from a select.
编辑：我的错，您希望选择在更新后显示结果，而不是从选择中更新。
Have you tried a sub-select?
您是否尝试过子选择？
update mytable set mydate = sysdate
where mydate in (select mydate from mytable where mydate is null);
回答by Kanagavelu Sugumar
in SQL 2008 a new TSQL statement "MERGE" is introduced which performs insert, update, or delete operations on a target table based on the results of a join with a source table. You can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
在 SQL 2008 中，引入了一个新的 TSQL 语句“MERGE”，它根据与源表的连接结果对目标表执行插入、更新或删除操作。您可以根据在另一个表中发现的差异，通过在一个表中插入、更新或删除行来同步两个表。
http://blogs.msdn.com/ajaiman/archive/2008/06/25/tsql-merge-statement-sql-2008.aspxhttp://msdn.microsoft.com/en-us/library/bb510625.aspx
http://blogs.msdn.com/ajaiman/archive/2008/06/25/tsql-merge-statement-sql-2008.aspx http://msdn.microsoft.com/en-us/library/bb510625.aspx
回答by zappan
if it's inside the transaction, the database locking system will take care of concurrency issues. of course, if you use one (the mssql default is that it uses lock, so it states if you don't override that)
如果它在事务内部，数据库锁定系统将处理并发问题。当然，如果你使用一个（mssql 默认是它使用锁，所以它说明你是否不覆盖它）
