ORACLE

Interbloqueos

Un interbloqueo se produce cuando dos o más sesiones a la espera de los datos bloqueados por la otra, dando lugar a todas las sesiones están bloqueadas. Oracle automáticamente detecta y resuelve los bloqueos por revertir el estado asociado a la transacción que detecta el punto muerto. Normalmente, los bloqueos son causados por bloqueo mal implementado en el código de aplicación. Este artículo muestra los pasos necesarios para identificar el código de la aplicación ofender cuando se detecta un interbloqueo.

Crea un usuario de prueba.

Creación de test usuario identificado por prueba DEFAULT TABLESPACE usuarios TABLESPACE temp TEMPORAL QUOTA UNLIMITED los usuarios;

GRANT CONNECT, CREATE TABLE para poner a prueba;GRANT EXECUTE ON DBMS_LOCK para poner a prueba;

Crear un esquema de prueba.

CONN test / prueba

CREATE TABLE deadlock_1 ( Número de identificación);

CREATE TABLE deadlock_2 ( Número de identificación);

INSERT INTO deadlock_1 (id) VALUES (1);INSERT INTO deadlock_2 (id) VALUES (1);COMMIT;

Inicie dos sesiones de SQL * Plus, cada uno conectado al usuario de prueba, a continuación, ejecute las siguientes piezas de código, uno en cada sesión.

- Ejecutar en la sesión 1.DECLARAR TIPO l_deadlock_1_id% deadlock_1.id; TIPO l_deadlock_2_id% deadlock_2.id;COMENZAR

- Bloquear las filas de la primera tabla. SELECT id EN l_deadlock_1_id DE deadlock_1 WHERE id = 1 Para la actualización; - Pausa. DBMS_LOCK.sleep (30);

- Bloquear las filas de la segunda tabla. SELECT id EN l_deadlock_2_id DE deadlock_2 WHERE id = 1 Para la actualización;

- Libera bloqueos. ROLLBACK;END;/

- Ejecutar en la sesión 2.DECLARAR TIPO l_deadlock_1_id% deadlock_1.id; TIPO l_deadlock_2_id% deadlock_2.id;COMENZAR - Bloquear las filas de la segunda tabla. SELECT id EN l_deadlock_2_id DE deadlock_2 WHERE id = 1 Para la actualización; - Pausa. DBMS_LOCK.sleep (30);

- Bloquear las filas de la primera tabla. SELECT id EN l_deadlock_1_id DE deadlock_1 WHERE id = 1 Para la actualización;

- Libera bloqueos. ROLLBACK;END;/

La primera pieza de código obtiene un bloqueo en una fila en la DEADLOCK_1 mesa, se detiene durante 30 segundos, a continuación, intenta obtener un bloqueo en una fila en la DEADLOCK_2 mesa. La segunda pieza de código hace lo mismo pero a la inversa, el bloqueo de una fila de la DEADLOCK_2 tabla, el DEADLOCK_1 mesa. La llamada a la DBMS_LOCK.SLEEP procedimiento sólo está presente para darle tiempo suficiente para cambiar de sesiones.

Finalmente, una de las sesiones se detecta el punto muerto, deshacer su transacción y producir un error callejón sin salida, mientras que la otra transacción se completa satisfactoriamente. Un error típico callejón sin salida se muestra a continuación.

ERROR en línea 1:ORA-00060: deadlock detectado a la espera de los recursosORA-06512: en línea 16

Además del error de interbloqueo a la sesión, un mensaje se coloca en el registro de alertas.

ORA-00060: Deadlock detectado. Más información en el archivo c: \ oracle \ product \ 10.2.0 \ admin \ db10g \ udump \ db10g_ora_4200.trc.

El mensaje de error contiene una referencia a un archivo de rastreo, cuyo contenido indican las instrucciones SQL bloqueados tanto en la sesión que ha detectado el punto muerto y las otras sesiones bloqueadas.

09/13/2006 09:11:40.646 ****** NOMBRE DE ACCIÓN: () 13/09/2006 09:11:40.615*** NOMBRE DEL MÓDULO: (SQL * Plus) 13/09/2006 09:11:40.615*** NOMBRE DEL SERVICIO: (SYS $ Users) 13/09/2006 09:11:40.615SESIÓN *** Identificación: (137.7008) 13/09/2006 09:11:40.615DEADLOCK DETECTADO[Deadlock transacciones]Instrucción SQL actual para esta sesión:SELECT ID DE DEADLOCK_2 WHERE ID = 1 FOR UPDATE----- PL / SQL Call Stack ----- objeto objeto de línea manejar nombre número16 1AFBE484 bloque anónimoEl punto muerto que sigue no es un error de ORACLE. Se trata de unpunto muerto debido a un error del usuario en el diseño de una aplicacióno de la emisión incorrecta ad-hoc de SQL. Las siguientesinformación puede ayudar a determinar el punto muerto:Deadlock gráfico: --------- Blocker (s) ----------------- Waiter (s) ---------Nombre del recurso sesión proceso mantiene esperas sesión proceso tiene esperaTX-0006001a 0000131b-19 137 X 24 159 XTX-00030028-00001313 24 159 X 19 137 Xsesión 137: DID 0001-0013-000067B7 sesión 159: DID desde 0001 hasta 0018-0000100Csesión 159: DID 0.001-0018-0000100C sesión 137: 0001-0013 DID-000067B7Filas esperó en:Sesión 159: obj - ROWID = 0000E6C7 - AAAObHAAEAAAABAAAA (Diccionario objn - 59079, expediente - 4, bloque - 64, ranura - 0)Sesión 137: obj - ROWID = 0000E6C8 - AAAObIAAEAAAABIAAA (Diccionario objn - 59080, expediente - 4, bloque - 72, ranura - 0)La información sobre las sesiones en espera:Sesión 159:

pid = 24 serial = 51383 = 43465 audsid usuario: 62/TEST O / S info: usuario: tim_hall, duración: winxp1, ospid: 5928:3844, máquina: winxp1 programa: sqlplusw.exe Nombre de la aplicación: SQL * Plus, valor hash = 3669949024 Actual Estado SQL: SELECT ID DE DEADLOCK_1 WHERE ID = 1 FOR UPDATEFin de la información sobre otras sesiones en espera. ========================================== =========

Las secciones en negrita son del mayor interés. En la primera sección se muestra la instrucción SQL bloqueado en la sesión que ha detectado el punto muerto. La segunda sección es un mensaje de Oracle diciendo que se trata de una cuestión de aplicación, no un error de Oracle. En la tercera sección se enumeran los estados bloqueados SQL en las otras sesiones en espera. Las sentencias de SQL que figuran en el archivo de seguimiento debería permitir identificar el código de la aplicación que está causando el problema.

Para resolver el problema, asegúrese de que las filas de las tablas siempre están encerrados en el mismo orden. Por ejemplo, en el caso de una relación maestro-detalle, usted puede decidir bloquear siempre una fila de la tabla maestra antes de bloquear una fila en la tabla de detalles.

En resumen, las medidas necesarias para identificar y rectificar los bloqueos que causan el código son:

Localice los mensajes de error en el registro de alertas. Busque el archivo de rastreo correspondiente (s). Identificar las sentencias SQL, tanto en el actual período de sesiones y la sesión de espera

(s). Utilice estas instrucciones SQL para identificar la parte del código que está teniendo

problemas. Alterar el código de la aplicación para evitar puntos muertos por bloqueo siempre filas de

las tablas en el mismo orden.

Detección de Deadlock

Oracle automáticamente detecta situaciones de punto muerto y los resuelve revirtiendo una de las declaraciones que participan en el interbloqueo, liberando así un conjunto de los bloqueos de fila en conflicto. Un mensaje correspondiente también se devuelve a la transacción que sufre de nivel de instrucción de reversión. La declaración deshace es el que pertenece a la transacción que detecta el punto muerto. Por lo general, la transacción señalada debería deshacer explícitamente, pero se puede volver a intentar la declaración enrollado de nuevo después de esperar.

Nota:En las transacciones distribuidas, estancamientos locales se detectan mediante el análisis de los datos de espera, y estancamientos globales son detectados por un tiempo de espera. Una vez detectado, no distribuidas y interbloqueos distribuidos son manejados por la base de datos y aplicación de la misma manera.

Interbloqueos ocurren con mayor frecuencia cuando las transacciones explícitamente anular el bloqueo por defecto de Oracle. Debido a que Oracle sí mismo no hace ninguna extensión de bloqueo y no utiliza bloqueos de lectura para las consultas, pero no uso el bloqueo de filas (en lugar de bloqueo a nivel de página), bloqueos ocurren con poca frecuencia en Oracle.

MySQL

Interbloqueos

Los deadlocks son un problema clásico de las bases de datos transaccionales, pero no son peligrosos a menos que sean tan frecuentes que no se puedan ejecutar en absoluto ciertas transacciones. Normalmente, las aplicaciones deben ser escritas de modo que esten preparadas para emitir nuevamente una transacción si ésta es cancelada debido a un deadlock.

InnoDB emplea bloqueos automáticos a nivel de fila. Se pueden producir deadlocks aún en el caso de transacciones que solamente insertan o eliminan una fila individual. Esto se debe a que estas operaciones no son realmente “atómicas”; sino que establecen automáticamente bloqueos enlos (posiblemente varios) registros de índice de la fila insertada o eliminada.

Con las siguientes técnicas se puede estar a cubierto de los deadlocks y reducir la probabilidad de que ocurran:

Emplear SHOW INNODB STATUS para determinar la causa del último deadlock. Puede ayudar a afinar la aplicación para evitar que ocurran otros.

Siempre hay que estar preparado para emitir nuevamente una transacción que haya fallado por un deadlock. Los deadlocks no revisten peligro, simplemente hay que intentar de nuevo.

Confirmar las transacciones frecuentemente. Las transacciones pequeñas son menos propensas a originar conflictos.

Si se están usando lecturas que establecen bloqueos (SELECT ... FOR UPDATE o ... LOCK IN SHARE MODE), hay que intentar utilizar un nivel de aislamiento bajo, como READ COMMITTED.

Acceder a las tablas y filas en un orden fijo. Entonces, las transacciones forman secuencias bien definidas y no originan deadlocks.

Agregar a las tablas índices adecuadamente elegidos. Entonces las consultas necesitarán examinar menos registros de índice y en consecuencia establecerán menos bloqueos. Utilizar EXPLAIN SELECT para determinar los índices que MySQL considera más apropiados para las consultas.

Utilizar menos el bloqueo. Si es aceptable que SELECT devuelva datos de una captura de la base de datos que no sea la más actualizada, no hay que agregarle las cláusulas FOR UPDATE o LOCK IN SHARE MODE. En este caso es adecuado utilizar el nivel de aislamiento READ COMMITTED, porque cada lectura consistente dentro de la misma transacción leerá de su propia captura más reciente.

Si nada de esto ayuda, habrá que serializar las transacciones con bloqueos a nivel de tabla. La forma correcta de emplear LOCK TABLES con tablas transaccionales, como InnoDB, es establecer AUTOCOMMIT = 0 y no invocar aUNLOCK TABLES hasta que se haya confirmado explícitamente la transacción. Por ejemplo, si se necesitara escribir en una tabla t1 y leer desde una tabla t2, se puede hacer esto:

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

[aquí se hace algo con las tablas t1 y t2];

COMMIT;

UNLOCK TABLES;

Los bloqueos a nivel de tabla favorecen el funcionamiento de la cola de transacciones, y evitan los deadlocks.

Otra manera de serializar transacciones es crear una tabla “semáforo” auxiliar que contenga sólo una fila. Hay que hacer que cada transacción actualice esa fila antes de acceder otras tablas. De ese modo, todas las transacciones se producirán en serie. Nótese que el algoritmo de detección instantánea de deadlocks de InnoDBtambién funciona en este caso, porque el bloqueo de serialización es un bloqueo a nivel de fila. Con los bloqueos a nivel de tabla de MySQL, debe emplearse el método de timeout para solucionar deadlocks.

En aquellas aplicaciones que emplean el comando de MySQL LOCK TABLES, MySQL no establece bloqueos de tabla si AUTOCOMMIT=1.

POSTGRE.

Interbloqueos

El uso de bloqueos explícitos puede aumentar la probabilidad de bloqueo de puertas , en los que

dos (o más) las operaciones de Seguros de las bodegas que el otro quiere. Por ejemplo, si una

transacción adquiere un bloqueo exclusivo en la tabla A y luego intenta adquirir un bloqueo

exclusivo en la tabla B, mientras que la transacción 2 ya tiene exclusivo de bloqueo de la tabla B

y ahora quiere un bloqueo exclusivo en la tabla A, entonces tampoco se puede

proceder . PostgreSQL automáticamente detecta situaciones de punto muerto y los resuelve

abortando una de las transacciones involucradas, permitiendo que el otro (s) para

completar. (Exactamente qué transacción se aborta es difícil de predecir y no se debe confiar en

ellas.)

Tenga en cuenta que los interbloqueos también puede ocurrir como resultado de bloqueos a

nivel de fila (y por tanto, pueden ocurrir incluso si el bloqueo explícito no se

utiliza).Consideremos el caso en el que dos transacciones simultáneas modificar una tabla. La

primera transacción ejecuta:

Cuentas UPDATE SET saldo = saldo + 100.00 DONDE acctnum = 11111;

Esto adquiere un bloqueo a nivel de fila en la fila con el número de cuenta especificado. A

continuación, la segunda transacción ejecuta:

Cuentas UPDATE SET saldo = saldo + 100.00 = DONDE acctnum 22222;

Cuentas UPDATE SET saldo = saldo - 100,00 DONDE acctnum = 11111;

La primera ACTUALIZACIÓN declaración con éxito adquiere un bloqueo a nivel de fila en la fila

especificada, por lo que tiene éxito en la actualización de la fila. Sin embargo, la

segunda ACTUALIZACIÓN instrucción halla que la fila que está intentando actualizar ya ha sido

bloqueado, por lo que espera a que la transacción que ha adquirido el bloqueo para

completar. Transacción dos está ahora a la espera de una transacción se complete antes de que

continúa la ejecución. Ahora, ejecuta una transacción:

Cuentas UPDATE SET saldo = saldo - 100,00 DONDE acctnum = 22222;

Transacción se intenta adquirir un bloqueo a nivel de fila en la fila especificada, pero no puede:

dos transacciones ya tiene una cerradura. Por lo tanto, espera transacción de dos a

completar. Por lo tanto, una transacción está bloqueada en operación dos, y dos transacción está

bloqueada en una transacción:. Una condición de interbloqueoPostgreSQL detectará esta

situación y abortará una de las transacciones.

La mejor defensa contra bloqueos suele evitarlos por tener la certeza de que todas las

aplicaciones que utilizan una base de datos adquiere bloqueos en varios objetos en un orden

coherente. En el ejemplo anterior, si ambas transacciones hubieran actualizado las filas en el

mismo orden, no se habría producido punto muerto. También hay que asegurarse de que el

primer bloqueo adquirido en un objeto en una transacción es el modo más restrictivo que serán

necesarios para ese objeto. Si no es factible para verificar esto con antelación, a continuación,

bloqueo de puertas puede ser manejado en la marcha por transacciones reintentando que

debido a interbloqueos abortar.

Siempre y cuando no se detecta la situación de bloqueo, una transacción que buscan ya sea a

nivel de tabla o el bloqueo a nivel de fila esperará indefinidamente bloqueos en conflicto al ser

puesto en libertad. Esto significa que es una mala idea para aplicaciones de celebrar

transacciones abiertas durante largos períodos de tiempo (por ejemplo, a la espera de la entrada

del usuario).

SQL SERVER

Detección de Deadlock

Todos los recursos mencionados en el apartado anterior participación en el régimen del motor de base de datos de detección de punto muerto. Deadlock detección se realiza mediante un subproceso de supervisión de bloqueo que, periódicamente, inicia una búsqueda a través de todas las tareas en una instancia del motor de base de datos. Los puntos siguientes describen el proceso de búsqueda:

El intervalo predeterminado es de 5 segundos.

Si el subproceso de supervisión de bloqueos encuentra interbloqueos, el intervalo de detección de punto muerto caerá desde 5 segundos hasta un mínimo de 100 milisegundos, dependiendo de la frecuencia de puntos muertos.

Si el subproceso de supervisión de bloqueo stops interbloqueos constatación, el motor de base aumenta los intervalos entre las búsquedas a 5 segundos.

Si un interbloqueo se acaba de detectar, se supone que los hilos próximos que debe esperará un bloqueo están entrando en el ciclo de interbloqueo. El primer par de bloqueo de espera después de un estancamiento ha sido detectado inmediatamente dará lugar a una búsqueda de interbloqueos en lugar de esperar a que el intervalo de detección de estancamiento que viene. Por ejemplo, si el intervalo actual es de 5 segundos y un punto muerto se detectó sólo, la espera de bloqueo junto comenzará el detector de punto muerto inmediatamente. Si esta espera de bloqueo forma parte de un callejón sin salida, se detectará de inmediato en lugar de estancamiento durante la búsqueda siguiente.

El motor de base de datos por lo general realiza la detección de estancamiento periódico solamente. Debido a que el número de puntos muertos encontrados en el sistema es generalmente pequeño, detección de estancamiento periódica ayuda a reducir los gastos indirectos de detección de estancamiento en el sistema.

Cuando el monitor de bloqueos inicia búsqueda de interbloqueos para un hilo en particular, identifica el recurso en el que el hilo está esperando. El monitor de bloqueo después encuentra el propietario (s) de ese recurso en particular y de forma recursiva continúa la búsqueda de interbloqueos para esos temas hasta que encuentra un ciclo.Un ciclo identificada de esta manera forma un punto muerto.

Después de un callejón sin salida es detectado, el motor de base de datos a un punto muerto termina eligiendo uno de los hilos como un sujeto del interbloqueo. El motor de base de datos termina el lote actual se está ejecutando para el hilo, retrotrae la transacción de la víctima del interbloqueo y devuelve un error 1205 para la aplicación.Anulación de la transacción para la víctima del interbloqueo libera todos los bloqueos mantenidos por la transacción. Esto permite que las transacciones de los otros hilos a ser bloqueado y continuar. El 1205 interbloqueo registros error información sobre los temas y los recursos implicados en un interbloqueo en el registro de errores.

Por defecto, el motor de base de datos elige como víctima del interbloqueo la sesión que ejecuta la transacción que es menos costoso de revertir. Alternativamente, un usuario puede especificar la prioridad de las sesiones en una situación de bloqueo se utiliza la instrucción DEADLOCK_PRIORITY SET. DEADLOCK_PRIORITY se puede ajustar a normal baja, o alto, o, alternativamente, se puede ajustar a cualquier valor entero en el rango de (-10 a 10). Los valores predeterminados de prioridad de interbloqueo en NORMAL. Si dos sesiones tienen distintas prioridades de interbloqueo, la sesión con la prioridad más baja es elegida como sujeto del interbloqueo. Si ambas sesiones tienen la misma prioridad de interbloqueo, la sesión con la transacción que es

menos costoso de revertir que se elija. Si las sesiones que participan en el ciclo de interbloqueo tienen la misma prioridad de interbloqueo y el mismo costo, una víctima es elegida al azar.

Cuando se trabaja con CLR, el monitor de interbloqueo detecta automáticamente estancamiento de los recursos de sincronización (monitores, lector / grabador de bloqueo de rosca y unirse) se accede dentro de los procedimientos administrados. Sin embargo, el punto muerto se resuelve lanzando una excepción en el procedimiento que se ha seleccionado para ser el sujeto del interbloqueo. Es importante entender que la excepción no se libera automáticamente los recursos actualmente propiedad de la víctima, los recursos deben ser explícitamente puestos en libertad. De acuerdo con el comportamiento de excepción, la excepción utilizada para identificar un interbloqueo puede ser capturada y despedida.

Sitios consultados:

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

http://www.postgresql.org/docs/9.1/static/explicit-locking.html

http://www.oracle-base.com/articles/misc/deadlocks.php

http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html