sql - rendimiento - ¿Puede una operación INSERT dar como resultado un punto muerto?

optimizar consultas sql server 2008 r2

Question

Asumiendo:

• Estoy utilizando el aislamiento de transacción REPEATABLE_READ o SERIALIZABLE (los bloqueos se retienen cada vez que accedo a una fila)
• Estamos hablando de múltiples hilos que acceden a múltiples tablas simultáneamente.

Tengo las siguientes preguntas:

1. ¿Es posible que una operación de INSERT provoque un interbloqueo? Si es así, proporcione un escenario detallado que demuestre cómo puede producirse un interbloqueo (por ejemplo, el subproceso 1 hace esto, el subproceso 2 hace eso, ..., interbloqueo).
2. Para puntos de bonificación: responda la misma pregunta para todas las demás operaciones (por ejemplo, SELECCIONAR, ACTUALIZAR, BORRAR).

ACTUALIZACIÓN : 3. Para puntos súper de bonificación: ¿cómo puedo evitar un interbloqueo en el siguiente escenario?

Dadas tablas:

• permisos [id BIGINT PRIMARY KEY]
• empresas [id BIGINT PRIMARY KEY, name VARCHAR(30), permission_id BIGINT NOT NULL, FOREIGN KEY (permission_id) REFERENCES permissions(id))

Creo una nueva empresa de la siguiente manera:

• INSERTA EN los permisos; - Inserta permissions.id = 100
• INSERT INTO companies (name, permission_id) VALUES (''Nintendo'', 100); - Inserciones companies.id = 200

Suprimo una empresa de la siguiente manera:

• SELECCIONE permission_id DE las compañías DONDE id = 200; - devuelve permission_id = 100
• ELIMINAR DE LAS EMPRESAS DONDE ID = 200;
• ELIMINAR DE los permisos DONDE id = 100;

En el ejemplo anterior, la orden de bloqueo INSERT es [permisos, empresas] mientras que la orden de bloqueo DELETE es [empresas, permisos]. ¿Hay alguna manera de corregir este ejemplo para el aislamiento REPEATABLE_READ o SERIALIZABLE ?

Answer 1

Además de la respuesta de LoztInSpace, las inserts pueden causar puntos muertos incluso sin deletes o updates presencia. Todo lo que necesita es un índice único y un orden de operaciones invertido.

Ejemplo en Oracle:

create table t1 (id number);
create unique index t1_pk on t1 (id);
--thread 1 :
insert into t1 values(1);
--thread 2
insert into t1 values(2);
--thread 1 :
insert into t1 values(2);
--thread 2
insert into t1 values(1); -- deadlock !

Answer 2

En general, todas las modificaciones pueden provocar un punto muerto y las selecciones no lo harán (llegaremos más adelante). Asi que

1. No, no puedes ignorar estos.
2. Puede ignorar un poco la selección dependiendo de su base de datos y configuración, pero los demás le darán puntos muertos.

Ni siquiera necesitas varias tablas.

La mejor manera de crear un interbloqueo es hacer lo mismo en un orden diferente.

Ejemplos de SQL Server:

create table A
(
PK int primary key
)

Sesión 1:

begin transaction
insert into A values(1)

Sesión 2:

begin transaction
insert into A values(7)

Sesión 1:

delete from A where PK=7

Sesión 2:

delete from A where PK=1

Obtendrás un punto muerto De modo que las inserciones y eliminaciones probadas pueden interrumpirse.

Las actualizaciones son similares:

Sesión 1:

begin transaction
insert into A values(1)
insert into A values(2)
commit
begin transaction
update A set PK=7 where PK=1

Sesión 2:

begin transaction
update A set pk=9 where pk=2
update A set pk=8 where pk=1

Sesión 1:

update A set pk=9 where pk=2

¡Punto muerto!

SELECT nunca debe interrumpir, pero en algunas bases de datos lo hará porque los bloqueos que utiliza interfieren con lecturas consistentes. Eso es sólo un mal diseño de motor de base de datos sin embargo.

SQL Server no se bloqueará en un SELECT si utiliza SNAPSHOT ISOLATION. Oracle y yo creemos que Postgres nunca se bloqueará en SELECT (a menos que tenga FOR FOR UPDATE, que claramente está reservándose para una actualización de todos modos).

Así que básicamente creo que tienes algunas suposiciones incorrectas. Creo que he probado:

1. Las actualizaciones pueden causar interbloqueos
2. Las eliminaciones pueden causar interbloqueos
3. Los insertos pueden causar puntos muertos.
4. No necesitas más de una mesa.
5. Necesitas más de una sesión

Tendrá que tomar mi palabra en SELECT;) pero dependerá de su base de datos y configuración.

Answer 3

Supongamos que tiene dos relaciones A y B y dos usuarios X e Y La tabla A está bloqueada por WRITE por el usuario X y la tabla B está bloqueada por WRITE por Y. A continuación, la siguiente consulta le dará un bloqueo si los usuarios X e Y lo utilizan.

Select * from A,B

Entonces, claramente, una operación de Select puede causar un punto muerto si las operaciones de unión que involucran más de una tabla son parte de ella. Por lo general, las operaciones de inserción y eliminación implican relaciones individuales. Así que no pueden causar un punto muerto.