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:
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?
This question is related to
sql
sql-server
tsql
sql-server-2008
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.
http://blogs.msdn.com/ajaiman/archive/2008/06/25/tsql-merge-statement-sql-2008.aspx http://msdn.microsoft.com/en-us/library/bb510625.aspx
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.
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.
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:
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
SET AlertDate = @UpdateTime --set value
WHERE CURRENT OF cur1;
FETCH NEXT FROM cur1;
END
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);
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
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)
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);
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
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)
It'd be easier to do your UPDATE first and then run 'SELECT ID FROM INSERTED'.
Take a look at SQL Tips for more info and examples.
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:
DECLARE @UpdatedIDs table (ID int)
UPDATE
Table1
SET
AlertDate = getutcdate()
OUTPUT
inserted.Id
INTO
@UpdatedIDs
WHERE
AlertDate IS NULL;
ADDED SEP 14, 2015:
"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 @UpdatedIDs if you need a single ID.
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
SYNCHRONOUSLY/ATOMICALLY perform (UPDATE then GET) in MYSQL
I have tried many options and found one that solved my issue.
1) OPTION_1 SELECT FOR UPDATE
This is maintaining the lock till update (SYNC from GET to UPDATE), but i need lock after update till the GET.
2) OPTION_2 Stored procedure
Stored procedure will not execute synchronously like redis lua, So there also we need sync code to perform that.
3) OPTION_3 Transaction
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.
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.
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;
}
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.
http://blogs.msdn.com/ajaiman/archive/2008/06/25/tsql-merge-statement-sql-2008.aspx http://msdn.microsoft.com/en-us/library/bb510625.aspx
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.
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.
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:
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
SET AlertDate = @UpdateTime --set value
WHERE CURRENT OF cur1;
FETCH NEXT FROM cur1;
END
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);
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.
http://blogs.msdn.com/ajaiman/archive/2008/06/25/tsql-merge-statement-sql-2008.aspx http://msdn.microsoft.com/en-us/library/bb510625.aspx
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);
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
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
It'd be easier to do your UPDATE first and then run 'SELECT ID FROM INSERTED'.
Take a look at SQL Tips for more info and examples.
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:
DECLARE @UpdatedIDs table (ID int)
UPDATE
Table1
SET
AlertDate = getutcdate()
OUTPUT
inserted.Id
INTO
@UpdatedIDs
WHERE
AlertDate IS NULL;
ADDED SEP 14, 2015:
"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 @UpdatedIDs if you need a single ID.
It'd be easier to do your UPDATE first and then run 'SELECT ID FROM INSERTED'.
Take a look at SQL Tips for more info and examples.
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.
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.
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:
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
SET AlertDate = @UpdateTime --set value
WHERE CURRENT OF cur1;
FETCH NEXT FROM cur1;
END
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.
http://blogs.msdn.com/ajaiman/archive/2008/06/25/tsql-merge-statement-sql-2008.aspx http://msdn.microsoft.com/en-us/library/bb510625.aspx
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
SYNCHRONOUSLY/ATOMICALLY perform (UPDATE then GET) in MYSQL
I have tried many options and found one that solved my issue.
1) OPTION_1 SELECT FOR UPDATE
This is maintaining the lock till update (SYNC from GET to UPDATE), but i need lock after update till the GET.
2) OPTION_2 Stored procedure
Stored procedure will not execute synchronously like redis lua, So there also we need sync code to perform that.
3) OPTION_3 Transaction
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.
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.
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;
}
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.
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.
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:
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
SET AlertDate = @UpdateTime --set value
WHERE CURRENT OF cur1;
FETCH NEXT FROM cur1;
END
Source: Stackoverflow.com