Há alguns dias, durante um período de contenção em um banco de dados Oracle, verifiquei que o ofensor era o wait event enq: TX - row lock contention. Quase que de imediato se associa esse evento com comandos como UPDATE, DELETE ou SELECT FOR UPDATE, porém para esse caso em específico a causa do lock foram INSERTS. Ao longo do artigo vou explicar como esse wait funciona e como INSERTS podem causá-lo.
Row Lock no Oracle
Quando um UPDATE, DELETE ou SELECT FOR UPDATE ocorrem durante uma sessão do banco de dados, essa transação adquire um lock exclusivo das linhas que ela está alterando, se tornando temporariamente o “dono” dessas linhas. Quaisquer outras sessões que venham a tentar alterar uma linha com um lock entram em espera, e esse tempo de espera é associado com o wait event enq: TX - row lock contention. Assim que a sessão que estava segurando o lock executa um commit ou rollback, liberando as linhas, a próxima sessão pode realizar as suas alterações.
Para fins exemplificiar, vou criar uma tabela de exemplo:
SQL> conn c##tester/senha Connected. SQL> CREATE TABLE t_lock_test (id NUMBER PRIMARY KEY, valor VARCHAR2(100)); Table created. SQL> INSERT INTO t_lock_test VALUES (1, 'registro original'); 1 row created. SQL> COMMIT; Commit complete.
Agora na primeira sessão vou adquirir o lock da linha da tabela executando um UPDATE na sessão (o mesmo pode ser alcançado com um DELETE ou SELECT FOR UPDATE):
SQL> UPDATE t_lock_test SET valor = 'modificado pela sessao 1' WHERE id = 1; 1 row updated.
Em seguida, em uma outra sessão, executo um segundo UPDATE, apontando para o mesmo id:
SQL> UPDATE t_lock_test SET valor = 'sessao 2 tentando modificar' WHERE id = 1;
Nesse caso a sessão fica presa, aguardando até que a primeira sessão finalize sua transação. As views V$SESSION e v$SQL podem nos dar mais informações sobre o problema:
SQL> col WAITER_USER for a220
SQL> col WAITER_USER for a20
SQL> col WAITER_SQL for a40
SQL> col BLOCKER_SQL for a40
SQL> col BLOCKER_USER for a20
SQL> col event for a30
SQL> SELECT
w.sid AS w_sid, -- waiting sid
w.serial# AS w_serial, -- waiting serial
w.username AS w_user, -- waiting user
w.event,
w.seconds_in_wait AS wsecs, -- waiting secs
ws.sql_text AS w_sql, -- waiting sql
b.sid AS b_sid, -- blocker sid
b.serial# AS b_serial, -- blocker serial
b.username AS b_user, -- blocker user
bs.sql_text AS b_sql -- blocker sql
FROM
gv$session w
JOIN gv$session b
ON w.blocking_session = b.sid
AND w.blocking_instance = b.inst_id
LEFT JOIN gv$sql ws ON w.sql_id = ws.sql_id AND w.inst_id = ws.inst_id
LEFT JOIN gv$sql bs ON b.prev_sql_id = bs.sql_id AND b.inst_id = bs.inst_id
WHERE
w.blocking_session IS NOT NULL
ORDER BY
b.inst_id, b.sid, w.seconds_in_wait DESC;
W_SID W_SERIAL W_USER EVENT WSECS W_SQL
---------- ---------- ---------- ------------------------------ ---------- ----------------------------------------
875 32868 C##TESTER enq: TX - row lock contention 37 UPDATE t_lock_test SET valor = 'sessao 2
tentando modificar' WHERE id = 1
B_SID B_SERIAL B_USER B_SQL
---------- ---------- ---------- ----------------------------------------
506 11891 C##TESTER UPDATE t_lock_test SET valor = 'modifica
do pela sessao 1' WHERE id = 1
Na consulta pode-se observar que a sessão de SID 506 está bloqueando a sessão de SID 875, gerando o wait event de enq: TX - row lock contention durante 37 segundos. Na V$LOCK é possível verificar mais informações sobre os locks da sessão bloqueadora:
SQL> COL NAME FOR A20
SQL> COL ID1_TAG FOR A20
SQL> COL ID2_TAG FOR A20
SQL> SELECT L.TYPE, T.NAME, LMODE, ID1, ID1_TAG, ID2, ID2_TAG FROM V$LOCK L JOIN V$LOCK_TYPE T ON T.TYPE = L.TYPE WHERE SID=506;
TY NAME LMODE ID1 ID1_TAG ID2 ID2_TAG
-- -------------------- ---------- ---------- -------------------- ---------- --------------------
AE Edition Lock 4 140 edition obj# 1 pdbuid
TM DML 3 74861 object # 0 table/partition
TX Transaction 6 655363 usn<<16 | slot 6113 sequence
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=74861;
OBJECT_NAME
------------------
T_LOCK_TEST
SQL> SELECT xidusn, xidslot, xidsqn, xid, status, start_time
FROM v$transaction
WHERE xidusn = 10 AND xidslot = 3 AND xidsqn = 6113;
XIDUSN XIDSLOT XIDSQN XID STATUS START_TIME
---------- ---------- ---------- ---------------- ---------------- --------------------
10 3 6113 0A000300E1170000 ACTIVE 06/06/26 12:47:44
O lock TM refere-se ao lock na tabela (object_id 74861), como modo 3 (Row Exclusive Table Lock), e ocorre durante DMLs para evitar que um DDL ocorra no objeto que está sendo alterado. Já o lock TX está se referindo à transação de sequence 6113, e está em modo 6 (Exclusive). Para identificar a linha específica afetada, a V$SESSION pode ser usada para montar o ROWID da linha, usando a sessão bloqueada:
SQL> col OBJECT_NAME for a30
SQL> select o.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create( 1, o.DATA_OBJECT_ID, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) as row_id
from v$session s, dba_objects o where sid=875 and s.ROW_WAIT_OBJ# = o.OBJECT_ID;
OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# ROW_ID
------------------------------ ------------- -------------- --------------- ------------- ------------------
T_LOCK_TEST 74861 1024 783 0 AAASRtAAAAAAAMPAAA
SQL> select * from c##tester.T_LOCK_TEST where rowid='AAASRtAAAAAAAMPAAA';
ID VALOR
---------- ----------------------------------------------------------------------------------------------------
1 registro original
Uma vez que a sessão bloqueadora finalize a transação, ou a sessão for eliminada, o wait envolvendo a segunda sessão acaba.
Porque INSERTs podem gerar Row Lock Contention?
Bom até aqui tudo faz sentido, uma vez que UPDATEs, DELETEs e SELECT FOR UPDATEs adquirem o lock da linha para uma modificação, faz sentido que novas sessões tentando acessar a mesma linha não possam realizar suas alterações, já que precisam acessar a versão mais nova dos dados para a garantia da consistência. Porém como INSERTs podem gerar isso?
Vamos de volta ao exemplo. Agora vou tentar realizar a inserção de uma linha com ID 2 na tabela (que é a primary key dessa tabela), sem realizar o commit:
SQL> INSERT INTO t_lock_test VALUES (2, 'Inserido pela sessão 1'); 1 row created.
Em outra sessão vou tentar executar um outro INSERT, usando o mesmo ID:
SQL> INSERT INTO t_lock_test VALUES (2, 'Inserido pela sessão 2');
Nesse momento, validando as sessões bloqueadoras, podemos identificar que a sessão 1 está segurando a sessão 2:
SQL> SELECT
w.sid AS w_sid, -- waiting sid
w.serial# AS w_serial, -- waiting serial
w.username AS w_user, -- waiting user
w.event,
w.seconds_in_wait AS wsecs, -- waiting secs
ws.sql_text AS w_sql, -- waiting sql
b.sid AS b_sid, -- blocker sid
b.serial# AS b_serial, -- blocker serial
b.username AS b_user, -- blocker user
bs.sql_text AS b_sql -- blocker sql
FROM
gv$session w
JOIN gv$session b
ON w.blocking_session = b.sid
AND w.blocking_instance = b.inst_id
LEFT JOIN gv$sql ws ON w.sql_id = ws.sql_id AND w.inst_id = ws.inst_id
LEFT JOIN gv$sql bs ON b.prev_sql_id = bs.sql_id AND b.inst_id = bs.inst_id
WHERE
w.blocking_session IS NOT NULL
ORDER BY
b.inst_id, b.sid, w.seconds_in_wait DESC;
W_SID W_SERIAL W_USER EVENT WSECS W_SQL
---------- ---------- ---------- ------------------------------ ---------- ----------------------------------------
875 32868 C##TESTER enq: TX - row lock contention 217 INSERT INTO t_lock_test VALUES (2, 'Inse
rido pela sessão 2')
B_SID B_SERIAL B_USER B_SQL
---------- ---------- ---------- ----------------------------------------
506 11891 C##TESTER INSERT INTO t_lock_test VALUES (2, 'Inse
rido pela sessão 1')
Nesse caso o enq: TX - row lock contention acontece devido à possível violação da Primary Key da tabela. A sessão 2 aguarda o commit ou rollback da sessão 1, para saber se a inserção ocorrerá com sucesso ou se receberá um erro ORA-0001, devido á violação da constraint. Resumindo: se duas sessões tentam inserir o mesmo valor para uma Primary Key ou Unique Key, haverá a ocorrência de enq: TX - row lock contention enquanto a primeira sessão não realizar o commit ou rollback da transação.
Existe um outro cenário onde INSERTs podem gerar essa contenção. Para a demonstração, vou criar duas tabelas, uma parent e outra child, que possui uma Foreign Key apontando para a tabela parent:
SQL> CREATE TABLE parent(
id NUMBER PRIMARY KEY,
name VARCHAR2(10)
);
Table created.
SQL> CREATE TABLE child(
id NUMBER PRIMARY KEY,
id_parent NUMBER ,
name VARCHAR2(10),
FOREIGN KEY (id_parent) REFERENCES parent(id)
);
Table created.
Na sequência, na sessão 1, vou realizar a inserção de uma chave pai, sem realizar o commit:
SQL> INSERT INTO parent VALUES(1, 'Parent 1'); 1 row created.
E na sessão 2, realizar a inserção na tabela filha:
SQL> INSERT INTO child VALUES(1, 1, 'Child 1');
Essa segunda sessão entra em espera:
SQL> SELECT
w.sid AS w_sid, -- waiting sid
w.serial# AS w_serial, -- waiting serial
w.username AS w_user, -- waiting user
w.event,
w.seconds_in_wait AS wsecs, -- waiting secs
ws.sql_text AS w_sql, -- waiting sql
b.sid AS b_sid, -- blocker sid
b.serial# AS b_serial, -- blocker serial
b.username AS b_user, -- blocker user
bs.sql_text AS b_sql -- blocker sql
FROM
gv$session w
JOIN gv$session b
ON w.blocking_session = b.sid
AND w.blocking_instance = b.inst_id
LEFT JOIN gv$sql ws ON w.sql_id = ws.sql_id AND w.inst_id = ws.inst_id
LEFT JOIN gv$sql bs ON b.prev_sql_id = bs.sql_id AND b.inst_id = bs.inst_id
WHERE
w.blocking_session IS NOT NULL
ORDER BY
b.inst_id, b.sid, w.seconds_in_wait DESC;
W_SID W_SERIAL W_USER EVENT WSECS W_SQL
---------- ---------- ---------- ------------------------------ ---------- -----------------------------------------
388 54642 C##TESTER enq: TX - row lock contention 36 INSERT INTO child VALUES(1, 1, 'Child 1')
B_SID B_SERIAL B_USER B_SQL
---------- ---------- ---------- ----------------------------------------
996 53106 C##TESTER INSERT INTO parent VALUES(1, 'Parent 1')
Novamente temos a ocorrência de enq: TX - row lock contention, mas desta vez relacionada à alteração da chave pai de um registro da tabela filha. Nesse caso a sessão 2 aguarda o commit ou rollback relacionado com a chave que seu INSERT está referenciando, para baser se a inserção ocorrerá normalmente ou um erro ORA-02291, devido á violação da chave pai inexistente.
Conclusão
Embora seja um tanto quanto contraintuitivo, INSERTs podem causar Row Lock Contention nesses dois cenários: concorrência na inserção da mesma chave primária ou única; e a inserção em tabelas filhas que referenciam uma chave pai ainda não commitada.