sqlmanagementstudio_x64_esn - sql server management studio express

¿Cómo lidiar con actualizaciones simultáneas en bases de datos?

Question

¿Cuál es la forma más común de lidiar con actualizaciones simultáneas en una base de datos SQL?

Considere un esquema SQL simple (restricciones y valores predeterminados no mostrados ...) como

create table credits (
  int id,
  int creds,
  int user_id
);

La intención es almacenar algún tipo de créditos para un usuario, por ejemplo, algo así como la reputación de stackoverflow.

¿Cómo lidiar con actualizaciones concurrentes a esa tabla? Algunas opciones:

• update credits set creds= 150 where userid = 1;

  En este caso, la aplicación retiró el valor actual, calculó el nuevo valor (150) y realizó una actualización. Lo cual deletrea un desastre si otra persona hace lo mismo al mismo tiempo. Supongo que envolver la recuperación del valor actual y actualizar en una transacción lo resolvería, por ejemplo, Begin; select creds from credits where userid=1; do application logic to calculate new value, update credits set credits = 160 where userid = 1; end; Begin; select creds from credits where userid=1; do application logic to calculate new value, update credits set credits = 160 where userid = 1; end; En este caso, podría verificar si el nuevo crédito sería <0 y simplemente truncarlo a 0 si los créditos negativos no tienen sentido.

• update credits set creds = creds - 150 where userid=1;

  Este caso no tendría que preocuparse por las actualizaciones concurrentes, ya que el DB se ocupa del problema de coherencia, pero tiene el defecto de creds que felizmente se volvería negativo, lo que podría no tener sentido para algunas aplicaciones.

Entonces, simplemente, ¿cuál es el método aceptado para lidiar con el problema (bastante simple) descrito anteriormente? ¿Qué pasa si el archivo db arroja un error?

Answer 1

El bloqueo optimista utilizando una nueva columna de timestamp puede resolver este problema de simultaneidad.

UPDATE credits SET creds = 150 WHERE userid = 1 and modified_data = old_modified_date

Answer 2

Envolver el código dentro de una transacción no es suficiente en algunos casos, independientemente del nivel de aislamiento que defina.

Supongamos que tiene estos pasos y 2 subprocesos de concurrencia:

1) open a transaction
2) fetch the data (SELECT creds FROM credits WHERE userid = 1;)
3) do your work (credits + amount)
4) update the data (UPDATE credits SET creds = ? WHERE userid = 1;)
5) commit

Y esta línea de tiempo:

Time =  0; creds = 100
Time =  1; ThreadA executes (1) and creates Txn1
Time =  2; ThreadB executes (1) and creates Txn2
Time =  3; ThreadA executes (2) and fetches 100
Time =  4; ThreadB executes (2) and fetches 100
Time =  5; ThreadA executes (3) and adds 100 + 50
Time =  6; ThreadB executes (3) and adds 100 + 50
Time =  7; ThreadA executes (4) and updates creds to 150
Time =  8; ThreadB tries to executes (4) but in the best scenario the transaction
          (depending of isolation level) won''t allow it and you get an error

La transacción evita que anule el valor de creds con un valor incorrecto, pero no es suficiente porque no quiero fallar ningún error.

Prefiero en cambio un proceso más lento que nunca falla y resolví el problema con un "bloqueo de fila de base de datos" en el momento en que obtengo los datos (paso 2) que evita que otros hilos puedan leer la misma fila hasta que haya terminado.

Hay pocas maneras de hacerlo en SQL Server y esta es una de ellas:

SELECT creds FROM credits WITH (UPDLOCK) WHERE userid = 1;

Si recreé la línea de tiempo anterior con esta mejora, obtienes algo como esto:

Time =  0; creds = 100
Time =  1; ThreadA executes (1) and creates Txn1
Time =  2; ThreadB executes (1) and creates Txn2
Time =  3; ThreadA executes (2) with lock and fetches 100
Time =  4; ThreadB tries executes (2) but the row is locked and 
                   it''s has to wait...

Time =  5; ThreadA executes (3) and adds 100 + 50
Time =  6; ThreadA executes (4) and updates creds to 150
Time =  7; ThreadA executes (5) and commits the Txn1

Time =  8; ThreadB was waiting up to this point and now is able to execute (2) 
                   with lock and fetches 150
Time =  9; ThreadB executes (3) and adds 150 + 50
Time = 10; ThreadB executes (4) and updates creds to 200
Time = 11; ThreadB executes (5) and commits the Txn2

Answer 3

Hay un punto crítico en su caso cuando disminuye el campo de crédito actual del usuario en una cantidad solicitada y si disminuyó con éxito usted realiza otras operaciones y el problema es en teoría que puede haber muchas solicitudes paralelas para disminuir la operación cuando, por ejemplo, el usuario 1 crédito en el saldo y con 5 solicitudes de cargos de crédito paralelas 1, puede comprar 5 cosas si la solicitud se enviará exactamente al mismo tiempo y usted terminará con -4 créditos en el saldo del usuario.

Para evitar esto , debe disminuir el valor actual de los créditos con el monto solicitado (en nuestro ejemplo, 1 crédito) y también verificar dónde, si el valor actual menos el monto solicitado es mayor o igual a cero :

ACTUALIZAR créditos SET creds = creds-1 WHERE creds-1> = 0 y userid = 1

Esto garantizará que el usuario nunca comprará muchas cosas con pocos créditos si va a pagar su sistema.

Después de esta consulta, debe ejecutar ROW_COUNT () que indica si el crédito del usuario actual cumplió los criterios y la fila se actualizó:

UPDATE credits SET creds = creds-1 WHERE creds-1>=0 and userid = 1
IF (ROW_COUNT()>0) THEN 
   --IF WE ARE HERE MEANS USER HAD SURELY ENOUGH CREDITS TO PURCHASE THINGS    
END IF;

Algo similar en PHP se puede hacer como:

mysqli_query ("UPDATE credits SET creds = creds-$amount WHERE creds-$amount>=0 and userid = $user");
if (mysqli_affected_rows())
{
   //do good things here
}

Aquí no usamos ni SELECCIONAR ... PARA ACTUALIZAR ni TRANSACCIÓN, pero si coloca este código dentro de la transacción, asegúrese de que ese nivel de transacción siempre proporcione los datos más recientes de la fila (incluidas las demás transacciones ya confirmadas). También puedes usar ROLLBACK si ROW_COUNT () = 0

La desventaja de WHERE credit- $ amount> = 0 sin row locking es:

Después de la actualización seguramente sabrá una cosa de que el usuario tiene suficiente saldo de crédito incluso si trata de hackear créditos con muchas solicitudes pero no sabe otras cosas como qué era el crédito antes del cobro (actualización) y qué era el crédito después del cobro (actualización).

Precaución:

No use esta estrategia dentro del nivel de transacción que no proporciona los datos de fila más recientes.

No use esta estrategia si quiere saber qué valor tenía antes y después de la actualización.

Solo trate de confiar en el hecho de que el crédito se cargó exitosamente sin ir por debajo de cero.

Answer 4

La tabla se puede modificar de la siguiente manera, introduzca una nueva versión de campo para manejar el bloqueo optimista. Esta es la forma más rentable y eficiente de lograr un mejor rendimiento en lugar de usar bloqueos en el nivel de la base de datos crear créditos de tabla (int id, int creds, int usuario_id, versión int);

seleccione creds, user_id, versión de créditos donde user_id = 1;

supongamos que esto devuelve creds = 100 y version = 1

los créditos de actualización establecen creds = creds * 10, version = version + 1 donde user_id = 1 y version = 1;

Siempre esto asegura que quien tenga el último número de versión solo puede actualizar este registro y no se permitirán las escrituras sucias

Answer 5

Para el primer escenario, puede agregar otra condición en la cláusula where para asegurarse de que no sobrescribirá los cambios realizados por un usuario concurrente. P.ej

update credits set creds= 150 where userid = 1 AND creds = 0;

Answer 6

Para las tablas MySQL InnoDB, esto realmente depende del nivel de aislamiento que establezca.

Si está utilizando el nivel predeterminado 3 (REPEATABLE READ), entonces deberá bloquear cualquier fila que afecte las escrituras subsiguientes, incluso si está en una transacción. En su ejemplo, necesitará:

SELECT FOR UPDATE creds FROM credits WHERE userid = 1;
-- calculate --
UPDATE credits SET creds = 150 WHERE userid = 1;

Si está utilizando el nivel 4 (SERIALIZABLE), basta con un SELECT simple seguido de una actualización. El nivel 4 en InnoDB se implementa mediante el bloqueo de lectura en cada fila que lees.

SELECT creds FROM credits WHERE userid = 1;
-- calculate --
UPDATE credits SET creds = 150 WHERE userid = 1;

Sin embargo, en este ejemplo específico, dado que el cálculo (agregar créditos) es lo suficientemente simple como para hacerse en SQL, un simple:

UPDATE credits set creds = creds - 150 where userid=1;

será equivalente a SELECCIONAR PARA ACTUALIZAR seguido de ACTUALIZAR.

Answer 7

Podría configurar un mecanismo de cola en el que las adiciones o sustracciones de un valor de tipo de rango se pondrían en cola para el procesamiento LIFO periódico por algún trabajo. Si se requiere información en tiempo real sobre el "saldo" de un rango, esto no encajaría porque el saldo no se computaría hasta que las entradas pendientes de la cola se reconcilien, pero si es algo que no requiere reconciliación inmediata, podría servir.

Esto parece reflejar, al menos desde afuera, cómo juegos como la antigua serie Panzer General manejan movimientos individuales. Se presenta el turno de un jugador y declaran sus movimientos. Cada movimiento por turno se procesa en secuencia, y no hay conflictos porque cada movimiento tiene su lugar en la cola.

Answer 8

Si almacena una última marca de tiempo de actualización con el registro, cuando lea el valor, lea también la marca de tiempo. Cuando vaya a actualizar el registro, verifique que coincida con la marca de tiempo. Si alguien viene detrás de usted y lo actualiza antes que usted, las marcas de tiempo no coincidirán.

Answer 9

Usar transacciones:

BEGIN WORK;
SELECT creds FROM credits WHERE userid = 1;
-- do your work
UPDATE credits SET creds = 150 WHERE userid = 1;
COMMIT;

Algunas notas importantes:

• No todos los tipos de bases de datos admiten transacciones. En particular, el tipo de base de datos por defecto de mysql, MyISAM, no. Use InnoDB si está en mysql.
• Las transacciones pueden abortarse debido a razones fuera de su control. Si esto sucede, su aplicación debe estar preparada para comenzar de nuevo, desde el BEGIN WORK.
• Tendrá que establecer el nivel de aislamiento en SERIALIZABLE, de lo contrario, la primera selección puede leer datos que otras transacciones aún no han confirmado (las transacciones no son como mutexes en los lenguajes de programación). Algunas bases de datos arrojarán un error si hay transacciones SERIALIZABLES continuas en curso, y deberá reiniciar la transacción.
• Algunos DBMS proporcionan SELECT .. PARA ACTUALIZAR, que bloqueará las filas recuperadas por seleccionar hasta que finalice la transacción.

La combinación de transacciones con procedimientos almacenados SQL puede hacer que la última parte sea más fácil de manejar; la aplicación simplemente llamará a un único procedimiento almacenado en una transacción, y lo volverá a llamar si la transacción aborta.