¿Cómo puedo detectar y liberar bloqueos en Amazon Redshift?

Descripción breve

El bloqueo es un mecanismo de protección que controla cuántas sesiones pueden acceder a una tabla al mismo tiempo. El bloqueo también determina qué operaciones se pueden realizar en esas sesiones. La mayoría de las bases de datos relacionales utilizan bloqueos a nivel de fila. Sin embargo, Amazon Redshift usa bloqueos a nivel de tabla. Puede experimentar conflictos de bloqueo si ejecuta sentencias DDL frecuentes en tablas de usuario o consultas DML.

Amazon Redshift tiene tres modos de bloqueo:

• AccessExclusiveLock: Se adquiere principalmente durante operaciones de DDL, como ALTER TABLE, DROP o TRUNCATE. AccessExclusiveLock impide todos los demás intentos de bloqueo.
• AccessShareLock: Se adquiere durante las operaciones de UNLOAD, SELECT, UPDATE o DELETE. AccessShareLock bloquea solo los intentos de AccessExclusiveLock. AccessShareLock no bloquea otras sesiones que intentan leer o escribir en la tabla.
• ShareRowExclusiveLock: Se adquiere durante las operaciones de COPY, INSERT, UPDATE o DELETE. ShareRowExclusiveLock bloquea AccessExclusiveLock y otros intentos de ShareRowExclusiveLock, pero no bloquea los intentos de AccessShareLock.

Cuando una consulta o transacción adquiere un bloqueo en una tabla, el bloqueo permanece mientras dure la consulta o transacción. Se bloquean otras consultas o transacciones que estén a la espera de adquirir el mismo bloqueo, excepto AccessShareLock. Para obtener más información sobre los motivos por los que una consulta puede bloquearse, consulte La consulta se bloquea.

Para resolver un problema de bloqueo, identifique la sesión (PID) que mantiene el bloqueo y, a continuación, finalice la sesión. Si la sesión no se finaliza, reinicie el clúster.

Resolución

Ejecute una consulta para identificar las sesiones que están bloqueadas:

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted='f' and b.granted='t'
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

El resultado es similar al siguiente:

txn_owner | txn_db |   xid   |  pid  |         txn_start          |      lock_mode      | table_id | tablename | granted | blocking_pid |        txn_duration         |
----------+--------+---------+-------+----------------------------+---------------------+----------+-----------+---------+--------------+-----------------------------+
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessExclusiveLock |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |
 usr1     | db1    | 5559927 | 20450 | 2018-06-30 10:52:19.761199 | AccessShareLock     |   351959 | lineorder | f       |        19813 | 0 days 0 hrs 0 mins 30 secs |
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessShareLock     |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |

Si el resultado de la columna granted es f (falso), significa que una transacción de otra sesión mantiene el bloqueo. La columna blocking_pid muestra el ID del proceso de la sesión que mantiene el bloqueo. En este ejemplo, el PID 19813 mantiene el bloqueo.

Para liberar un bloqueo, espere a que finalice la transacción que mantiene el bloqueo. También puede finalizar la sesión manualmente mediante la ejecución del siguiente comando:

select pg_terminate_backend(PID);

Al finalizar un PID, se anulan todas las transacciones en ejecución y se liberan todos los bloqueos de la sesión. Luego, las demás transacciones que están a la espera de adquirir el bloqueo reclaman los bloqueos.

Nota: El valor «1» devuelto por PG_TERMINATE_BACKEND(PID) normalmente indica que la solicitud de finalización del PID se ha realizado correctamente. Sin embargo, esto no garantiza que el PID realmente haya finalizado. En algunos casos, el PID no se puede determinar en función de su estado interno. Por lo tanto, se recomienda comprobar también STV_SESSIONS (y otras tablas del sistema relevantes) para confirmar si el PID realmente ha finalizado.

Si PG_TERMINATE_BACKEND(PID) no finaliza correctamente la sesión, reinicie el clúster para finalizar el proceso. El reinicio se puede realizar mediante un reinicio del clúster a través de la consola de Amazon Redshift o mediante un reinicio de la base de datos mediante REBOOT_CLUSTER.

Información relacionada

LOCK (Amazon Redshift)

Posible situación de interbloqueo para transacciones de escritura simultáneas