transaction - liquibase sql preconditions

Oracle selecciona para comportamiento de actualización

Question

El problema que intentamos resolver se ve así.

• Tenemos una mesa llena de filas que representan tarjetas. El propósito de la transacción de reserva es asignar una tarjeta a un cliente
• Una tarjeta no puede pertenecer a muchos clientes.
• Después de algún tiempo (si no se compra), una tarjeta debe devolverse al conjunto de recursos disponibles
• La reserva puede ser hecha por muchos clientes al mismo tiempo
• Utilizamos la base de datos Oracle para almacenar los datos, por lo que la solución debe funcionar al menos en Oracle 11

Nuestra solución es asignar un estado a la tarjeta y guardar la fecha de reserva. Al reservar una tarjeta, lo hacemos utilizando la declaración "seleccionar para actualizar". La consulta busca las tarjetas disponibles y las que se reservaron hace mucho tiempo.

Sin embargo, nuestra consulta no funciona como se esperaba.

He preparado una situación simplificada para explicar el problema. Tenemos una tabla de números de tarjeta, llena de datos: todas las filas tienen números de identificación que no son nulos. Ahora, vamos a tratar de bloquear algunos de ellos.

-- first, in session 1
set autocommit off;
select id from card_numbers
where id is not null
and rownum <= 1
for update skip locked;

No confirmamos la transacción aquí, la fila debe estar bloqueada.

-- later, in session 2
set autocommit off;
select id from card_numbers
where id is not null
and rownum <= 1
for update skip locked;

El comportamiento esperado es que en ambas sesiones obtenemos una fila única y diferente que satisface las condiciones de consulta.

Sin embargo, no funciona de esa manera. Dependiendo de si usamos o no la parte "omitida bloqueada" de la consulta, los cambios de comportamiento:

• sin "omitir bloqueado": la segunda sesión está bloqueada, esperando la confirmación de transacción o la reversión en la sesión uno
• con "saltar bloqueado" - la segunda consulta devuelve el conjunto de resultados inmediatamente vacío

Entonces, después de esta larga introducción, viene la pregunta.

¿Es posible el tipo de comportamiento de bloqueo deseado en Oracle? Si es así, ¿qué estamos haciendo mal? ¿Cuál sería la solución correcta?

Answer 1

El comportamiento que has encontrado para FOR UPDATE SKIP LOCKED se ha descrito en esta nota de blog . Entiendo que la cláusula FOR UPDATE se evalúa DESPUÉS de la cláusula WHERE. SKIP LOCKED es como un filtro adicional que garantiza que, entre las filas que se habrían devuelto, ninguna se bloquea.

Su declaración es lógicamente equivalente a: busque la primera fila de card_numbers y devuélvala si no está bloqueada. Obviamente esto no es lo que quieres.

Aquí hay un pequeño caso de prueba que reproduce el comportamiento que describe:

SQL> CREATE TABLE t (ID PRIMARY KEY)
2 AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;
Table created
SESSION1> select id from t where rownum <= 1 for update skip locked;
ID
----------
1
SESSION2> select id from t where rownum <= 1 for update skip locked;
ID
----------

No se devuelve ninguna fila desde la segunda selección. Puede utilizar un cursor para solucionar este problema:

SQL> CREATE FUNCTION get_and_lock RETURN NUMBER IS
2 CURSOR c IS SELECT ID FROM t FOR UPDATE SKIP LOCKED;
3 l_id NUMBER;
4 BEGIN
5 OPEN c;
6 FETCH c INTO l_id;
7 CLOSE c;
8 RETURN l_id;
9 END;
10 /
Function created
SESSION1> variable x number;
SESSION1> exec :x := get_and_lock;
PL/SQL procedure successfully completed
x
---------
1
SESSION2> variable x number;
SESSION2> exec :x := get_and_lock;
PL/SQL procedure successfully completed
x
---------
2

Ya que he buscado explícitamente el cursor, solo se devolverá una fila (y solo se bloqueará una fila).

Answer 2

Mientras que las otras respuestas ya explicaron suficientemente lo que está pasando en su base de datos con las diversas variantes de SELECT .. FOR UPDATE , creo que vale la pena mencionar que Oracle no recomienda el uso de FOR UPDATE SKIP LOCKED directamente y alienta a usar Oracle AQ lugar:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346

Usamos Oracle AQ en nuestra aplicación y puedo confirmar que, después de una curva de aprendizaje algo pronunciada, puede ser una forma bastante conveniente de manejar productores / consumidores directamente en la base de datos.

Answer 3

No es que la respuesta de Vincent sea incorrecta, pero la habría diseñado de otra manera.

Mi primer instinto es seleccionar para actualizar el primer registro disponible y actualizar el registro con una "fecha_ reservada". Después de que XXX haya transcurrido el tiempo y la transacción no se haya finalizado, actualice la fecha_ reservada del registro de nuevo a nulo, liberando el registro nuevamente.

Intento mantener las cosas lo más simples posible. Para mí, esto es más simple.