sql server 2005 - ver - ¿La ejecución del procedimiento almacenado de T-SQL es "atómica"?

stored procedure sql tutorial

Question

Digamos que tengo un procedimiento almacenado simple que se ve así (nota: esto es solo un ejemplo, no un procedimiento práctico):

CREATE PROCEDURE incrementCounter AS
DECLARE @current int
SET @current = (select CounterColumn from MyTable) + 1
UPDATE
MyTable
SET
CounterColumn = current
GO

Estamos asumiendo que tengo una tabla llamada ''myTable'' que contiene una fila, con ''CounterColumn'' que contiene nuestro recuento actual.

¿Se puede ejecutar este procedimiento almacenado varias veces, al mismo tiempo?

es decir, esto es posible:

Llamo ''incrementCounter'' dos veces. La llamada A llega al punto donde establece la variable ''actual'' (digamos que es 5). La llamada B llega al punto donde establece la variable ''actual'' (que también sería 5). La llamada A finaliza la ejecución, luego finaliza la llamada B. Al final, la tabla debe contener el valor de 6, pero en su lugar contiene 5 debido a la superposición de la ejecución

Answer 1

Tal vez estoy leyendo demasiado en su ejemplo (y su situación real puede ser significativamente más complicada), pero ¿por qué no haría esto en una sola declaración?

CREATE PROCEDURE incrementCounter AS
UPDATE
MyTable
SET
CounterColumn = CounterColumn + 1
GO

De esta forma, es automáticamente atómico y si se ejecutan dos actualizaciones al mismo tiempo, siempre serán ordenadas por SQL Server para evitar el conflicto que usted describe. Sin embargo, si su situación real es mucho más complicada, envolverla en una transacción es la mejor manera de hacerlo.

Sin embargo, si otro proceso ha habilitado un nivel de aislamiento "menos seguro" (como uno que permite lecturas sucias o lecturas no repetibles), entonces no creo que una transacción lo proteja, ya que otro proceso puede ver en el parcialmente actualizado datos si se elige para permitir lecturas inseguras.

Answer 2

Esto es para SQL Server.

Cada declaración es atómica, pero si desea que el procedimiento almacenado sea atómico (o cualquier secuencia de enunciados en general), debe rodear explícitamente las declaraciones con

COMIENZA LA TRANSACCIÓN
Declaración ...
Declaración ...
COMPROMETER LA TRANSACCIÓN

(Es común usar BEGIN TRAN y END TRAN para abreviar).

Por supuesto, hay muchas maneras de entrar en problemas de bloqueo dependiendo de lo que esté sucediendo al mismo tiempo, por lo que puede necesitar una estrategia para tratar las transacciones fallidas. (Una discusión completa de todas las circunstancias que pueden resultar en bloqueos, sin importar cómo concibas este SP en particular, está más allá del alcance de la pregunta). Pero todavía serán reenviables debido a la atomicidad. Y en mi experiencia probablemente estarás bien, sin saber sobre tus volúmenes de transacciones y las otras actividades en la base de datos. Disculpe por decir lo obvio.

Contrariamente a una idea errónea popular, esto funcionará en su caso con la configuración predeterminada del nivel de transacción.

Answer 3

Además de colocar el código entre BEGIN TRANSACTION y END TRANSACTION , deberá asegurarse de que su nivel de aislamiento de transacción esté configurado correctamente.

Por ejemplo, el nivel de aislamiento SERIALIZABLE evitará la pérdida de actualizaciones cuando el código se ejecute simultáneamente, pero READ COMMITTED (el valor predeterminado en SQL Server Management Studio) no lo hará.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Como otros ya han mencionado, al tiempo que se garantiza la coherencia, esto puede causar bloqueos y bloqueos, por lo que puede no ser la mejor solución en la práctica.

Answer 4

Yo uso este método

CREATE PROCEDURE incrementCounter
AS
DECLARE @current int
UPDATE MyTable
SET
@current = CounterColumn = CounterColumn + 1
Return @current

este procedimiento hace los dos comandos a la vez y está aislado de otra transacción.

Answer 5

La respuesta breve a su pregunta es SÍ, puede y quedará corta. Si desea bloquear la ejecución simultánea de procedimientos almacenados, inicie una transacción y actualice los mismos datos en cada ejecución del procedimiento almacenado antes de continuar haciendo cualquier trabajo dentro del procedimiento.

CREATE PROCEDURE ..
BEGIN TRANSACTION
UPDATE mylock SET ref = ref + 1
...

Esto obligará a otras ejecuciones simultáneas a esperar su turno, ya que no podrán cambiar el valor de ''ref'' hasta que se completen las otras transacciones y se levante el bloqueo de actualización asociado.

En general, es una buena idea suponer que el resultado de cualquiera y todas las consultas SELECT son obsoletas incluso antes de que se ejecuten. Usar niveles de aislamiento "pesados" para solucionar esta desafortunada realidad limita seriamente la escalabilidad. Es mucho mejor estructurar los cambios de una manera que haga suposiciones optimistas sobre el estado del sistema que espera que exista durante la actualización, de modo que cuando su suposición falla, puede volver a intentarlo más tarde y esperar un mejor resultado. Por ejemplo:

UPDATE
MyTable
SET
CounterColumn = current
WHERE CounterColumn = current - 1

Usando su ejemplo con la cláusula WHERE agregada, esta actualización no afecta a ninguna fila si falla la suposición sobre su estado actual. Marque @@ ROWCOUNT para probar el número de filas y la reversión o alguna otra acción según corresponda mientras difiera del resultado esperado.