Я хотел бы обновить набор строк на основе простых критериев и получить список PK, которые были изменены. Я думал, что могу просто сделать что-то подобное, но беспокоюсь о возможных проблемах concurrency:
SELECT Id FROM Table1 WHERE AlertDate IS NULL;
UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL;
Если это связано с транзакцией, существуют ли какие-либо проблемы concurrency, которые могут возникнуть? Или есть лучший способ сделать это?
Рассмотрите возможность OUTPUT в UPDATE (также DELETE и INSERT). Пример со связанной страницы MSDN:
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO @MyTableVar;
Один из способов справиться с этим - сделать это в транзакции и сделать запрос SELECT блокировкой обновлений для выбранных строк до завершения транзакции.
BEGIN TRAN
SELECT Id FROM Table1 WITH (UPDLOCK)
WHERE AlertDate IS NULL;
UPDATE Table1 SET AlertDate = getutcdate()
WHERE AlertDate IS NULL;
COMMIT TRAN
Это исключает возможность того, что одновременный клиент обновляет строки, выбранные в момент между вашим SELECT и вашим UPDATE.
Когда вы совершаете транзакцию, блокировки обновлений будут освобождены.
Другой способ справиться с этим - объявить курсор для вашего SELECT с опцией FOR UPDATE. Затем ОБНОВЛЯЙТЕ, ЧТО ТЕКУЩИЙ КУРСОР. Следующее не проверено, но должно дать вам основную идею:
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
Сначала было бы проще выполнить UPDATE, а затем запустить "SELECT ID FROM INSERTED".
Взгляните на Советы SQL для получения дополнительной информации и примеров.
Много лет спустя...
Принятый ответ использования предложения OUTPUT хорош. Мне пришлось выкопать фактический синтаксис, так что вот оно:
DECLARE @UpdatedIDs table (ID int)
UPDATE
Table1
SET
AlertDate = getutcdate()
OUTPUT
inserted.Id
INTO
@UpdatedIDs
WHERE
AlertDate IS NULL;
ADDED SEP 14, 2015:
"Можно ли использовать переменную таблицы вместо переменной таблицы?" можно спросить... Извините, но вы не можете. Вы должны будете SELECT @SomeID = ID from @UpdatedIDs, если вам нужен один идентификатор.
Возможно, что-то более похожее на это?
declare @UpdateTime datetime
set @UpdateTime = getutcdate()
update Table1 set AlertDate = @UpdateTime where AlertDate is null
select ID from Table1 where AlertDate = @UpdateTime
если он внутри транзакции, система блокировки базы данных позаботится о проблемах concurrency. конечно, если вы используете один (по умолчанию mssql используется блокировка, поэтому он указывает, не перекрывает ли это)
Изменить: мой плохой, вам нужно, чтобы выбор показывал результаты после обновления, а не обновлялся с помощью выбора.
Вы пробовали суб-выбор?
update mytable set mydate = sysdate
where mydate in (select mydate from mytable where mydate is null);
в SQL 2008 введен новый оператор TSQL "MERGE", который выполняет операции вставки, обновления или удаления в целевой таблице на основе результатов объединения с исходной таблицей. Вы можете синхронизировать две таблицы, вставляя, обновляя или удаляя строки в одной таблице на основе различий, найденных в другой таблице.
http://blogs.msdn.com/ajaiman/archive/2008/06/25/tsql-merge-statement-sql-2008.aspx http://msdn.microsoft.com/en-us/library/bb510625.aspx
Я столкнулся с той же проблемой; Я должен обновить сумму кредита и должен получить измененное время, а также данные о кредитах от БД. Это в основном
SYNCHRONOUSLY/ATOMICALLY выполнить (UPDATE, затем GET) в MYSQL
Я попробовал много вариантов и нашел тот, который решил мою проблему.
1) OPTION_1 SELECT FOR UPDATE
Это поддерживает блокировку до обновления (SYNC от GET до UPDATE), но мне нужно блокировать после обновления до GET.
2) OPTION_2 Хранимая процедура
Сохраненная процедура не будет выполняться синхронно, как redis lua. Поэтому нам также нужен код синхронизации для выполнения.
3) OPTION_3 Транзакция
Я использовал entityManager JPA, как показано ниже, подумал, что перед фиксацией никто не может обновить, и до фиксации я получу обновленный объект вместе с измененным временем (из БД). Но я не получил последний объект. Только совершить, я получил последние.
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 решил проблему, поэтому перед обновлением я приобрел блокировку; то после 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;
}