transaction - En MS SQL Server, ¿hay alguna manera de incrementar "atómicamente" una columna que se usa como contador?

try catch sql server 2005

Question

Asumiendo una configuración de aislamiento de transacción de ReadCommitted Snapshot, ¿la siguiente declaración es "atómica" en el sentido de que nunca "perderá" un incremento simultáneo?

update mytable set counter = counter + 1

Supongo que, en el caso general, donde esta declaración de actualización forma parte de una transacción más grande, no sería así. Por ejemplo, creo que este escenario es posible:

• actualizar el contador dentro de la transacción n. ° 1
• hacer algunas otras cosas en la transacción n. ° 1
• actualizar el contador con la transacción n. ° 2
• comprometer transacción # 2
• cometer la transacción n. ° 1

En esta situación, ¿el contador no se incrementaría solo en 1? ¿Hace una diferencia si esa es la única declaración en una transacción?

¿Cómo maneja esto un sitio como stackoverflow para su contador de preguntas? ¿O la posibilidad de "perder" algunos incrementos es considerada aceptable?

Answer 1

No, no es. El valor se lee en modo compartido y luego se actualiza en modo exclusivo, por lo que se pueden realizar lecturas múltiples.

O usa el nivel Serializable o usa algo como

update t
set counter = counter+1
from t with(updlock, <some other hints maybe>)
where foo = bar

Answer 2

En el fondo hay una sola transacción, la más externa. Las transacciones internas son más como puntos de control dentro de una transacción. Los niveles de aislamiento afectan solo a las transacciones más externas de los hermanos, no a las transacciones relacionadas entre padres e hijos.

El contador se incrementará en dos. Lo siguiente produce una fila con un valor de (Num = 3). (Abrí SMSS y lo apunté a una instancia local de SQL Server 2008 Express. Tengo una base de datos llamada Playground para probar cosas).

use Playground
drop table C
create table C (
Num int not null)
insert into C (Num) values (1)
begin tran X
update C set Num = Num + 1
begin tran Y
update C set Num = Num + 1
commit tran Y
commit tran X
select * from C

Answer 3

Read Committed Snapshot solo trata con bloqueos al seleccionar datos de tablas.

Sin embargo, en t1 y t2, está ACTUALIZANDO los datos, que es un escenario diferente.

Cuando ACTUALIZA el contador, escalará a un bloqueo de escritura (en la fila), impidiendo que se produzca la otra actualización. t2 podría leer, pero t2 se bloqueará en su ACTUALIZACIÓN hasta que se complete t1, y t2 no podrá confirmar antes de t1 (lo cual es contrario a su línea de tiempo). Solo una de las transacciones obtendrá la actualización del contador, por lo tanto ambas actualizarán el contador correctamente dado el código presentado. (probado)

• contador = 0
• t1 contador de actualización (contador => 1)
• contador de actualización t2 (bloqueado)
• t1 commit (contador = 1)
• t2 desbloqueado (ahora puede actualizar el contador) (contador => 2)
• t2 commit

Read Committed significa que solo puede leer valores confirmados, pero eso no significa que tenga lecturas repetibles. Por lo tanto, si usa y depende de la variable del contador, y tiene la intención de actualizarla más tarde, es posible que esté ejecutando las transacciones en el nivel de aislamiento incorrecto.

Puede usar un bloqueo de lectura repetible, o si solo actualiza el contador algunas veces, puede hacerlo usted mismo usando una técnica de bloqueo optimista. por ejemplo, una columna de marca de tiempo con la tabla de contador, o una actualización condicional.

DECLARE @CounterInitialValue INT
DECLARE @NewCounterValue INT
SELECT @CounterInitialValue = SELECT counter FROM MyTable WHERE MyID = 1234
-- do stuff with the counter value
UPDATE MyTable
SET counter = counter + 1
WHERE
MyID = 1234
AND
counter = @CounterInitialValue -- prevents the update if counter changed.
-- the value of counter must not change in this scenario.
-- so we rollback if the update affected no rows
IF( @@ROWCOUNT = 0 )
ROLLBACK

Este artículo de devx es informativo, aunque habla sobre las características cuando todavía estaban en fase beta, por lo que puede no ser completamente preciso.

Actualización: Como indica Justice, si t2 es una transacción anidada en t1, la semántica es diferente. Nuevamente, ambos actualizarían el contador correctamente (+2) porque desde la perspectiva de t2 dentro de t1, el contador ya se actualizó una vez. El t2 anidado no tiene acceso al contador antes de actualizarlo.

• contador = 0
• t1 contador de actualización (contador => 1)
• contador de actualización t2 (transacción anidada) (contador => 2)
• t2 commit
• t1 commit (contador = 2)

Con una transacción anidada, si t1 emite ROLLBACK después de t1 COMMIT, el contador vuelve a su valor original porque también deshace la confirmación de t2.

Answer 4

De acuerdo con la Ayuda de MSSQL, puedes hacerlo así:

UPDATE tablename SET counterfield = counterfield + 1 OUTPUT INSERTED.counterfield

Esto actualizará el campo en uno y devolverá el valor actualizado como un conjunto de registros SQL.