부모 행을 지우거나 키를 바꿀 때 DB 는 자식 표에 그 부모를 참조하는 행이 있는지 봅니다. 자식 표의 외래 키 컬럼에 인덱스가 없으면 자식 표를 넓게 잠글 수 있습니다.
parent, child 표로 자식 표의 인덱스를 조회합니다. H2 는 외래 키 컬럼 인덱스를 자동으로 만들어 Oracle·MSSQL 과 다릅니다.
SELECT
column_name
, ordinal_position
FROM INFORMATION_SCHEMA.INDEX_COLUMNS
WHERE table_name = 'CHILD'
ORDER BY index_name, ordinal_position;COLUMN_NAME | ORDINAL_POSITION
------------+-----------------
PARENT_ID | 1
ID | 1
(2행)PARENT_ID 에 인덱스가 있고 PK 인 ID 에도 있습니다. 외래 키 컬럼 중 앞자리에 인덱스가 없는 것만 찾는 쿼리는 아래처럼 씁니다.
SELECT
k.table_name
, k.column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
WHERE k.constraint_name = 'FK_CHILD_PARENT'
AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.INDEX_COLUMNS i WHERE i.table_name = k.table_name AND i.column_name = k.column_name AND i.ordinal_position = 1);TABLE_NAME | COLUMN_NAME
-----------+------------
(0행)H2 는 자동 생성 때문에 항상 0행입니다. Oracle 이나 MSSQL 에서는 각 DB 의 인덱스 뷰(Oracle 은 USER_IND_COLUMNS, MSSQL 은 sys.index_columns)로 같은 조인을 만들면 인덱스 없는 외래 키가 나옵니다. 나온 컬럼에는 인덱스를 추가합니다.
부모 행 삭제가 자식 때문에 막히는 것도 확인합니다.
-- @error
DELETE FROM parent WHERE id = 1;예상 오류: Referential integrity constraint violation: "FK_CHILD_PARENT: PUBLIC.CHILD FOREIGN KEY(PARENT_ID) REFERENCES PUBLIC.PARENT(ID) (1)"이 검사가 자식 표를 찾을 때 인덱스가 있어야 빠르고 좁게 잠급니다. 인덱스가 없는 Oracle 에서는 부모 행 삭제가 자식 표 전체 잠금으로 번질 수 있습니다.
누가 누구를 막고 있는지는 각 DB 의 조회 뷰로 봅니다. H2 는 이 뷰들이 없어서 실행하지 않고, 아래 결과는 예제 데이터로 그린 도식입니다.
-- Oracle · Tibero
SELECT
sid
, serial#
, username
, blocking_session
, event
FROM v$session
WHERE blocking_session IS NOT NULL;Oracle 은 V$SESSION 의 BLOCKING_SESSION(10g 부터)이 막고 있는 세션의 SID 를 줍니다. 잠금 자체는 V$LOCK 에서 봅니다.
도식(H2 미지원, 실행 결과 아님). 세션 B(SID 42)가 세션 A(SID 17)에게 막힌 상태입니다.
SID | SERIAL# | USERNAME | BLOCKING_SESSION | EVENT
----+---------+----------+------------------+------------------------------
42 | 5310 | APP | 17 | enq: TX - row lock contention-- MySQL 8.0
SELECT
waiting_pid
, waiting_query
, blocking_pid
FROM sys.innodb_lock_waits;MySQL 8.0 부터 performance_schema.data_locks 와 data_lock_waits 로 잠금과 대기를 볼 수 있고, sys.innodb_lock_waits 는 이를 읽기 좋게 묶은 뷰입니다. 가장 최근 데드락은 SHOW ENGINE INNODB STATUS 의 LATEST DETECTED DEADLOCK 절에 남습니다.
도식(H2 미지원, 실행 결과 아님).
waiting_pid | waiting_query | blocking_pid
------------+----------------------------------+-------------
28 | UPDATE account SET balance = ... | 25-- MSSQL
SELECT
session_id
, blocking_session_id
, wait_type
, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;MSSQL 은 sys.dm_exec_requests 의 blocking_session_id 가 0 이 아닌 요청이 대기 중인 세션입니다. 어떤 자원이 잠겼는지는 sys.dm_tran_locks 에서 봅니다.
도식(H2 미지원, 실행 결과 아님).
session_id | blocking_session_id | wait_type | wait_time
-----------+---------------------+-----------+----------
55 | 52 | LCK_M_U | 31200조회로 막고 있는 세션을 찾은 뒤 그 세션이 무엇을 하는지 확인하고, 정말 필요할 때만 세션을 종료합니다. 종료된 세션의 트랜잭션은 롤백되므로 확인 없이 끊지 않습니다.
예제 2 의 H2 문법과 달리 "N건만 잡기" 를 쓰는 모양이 DB마다 다릅니다. 아래는 문법 검토만 한 것입니다.
-- MySQL 8.0
SELECT id FROM job_queue WHERE status = 'WAIT' ORDER BY id LIMIT 3 FOR UPDATE SKIP LOCKED;-- MSSQL
SELECT TOP 3 id FROM job_queue WITH (UPDLOCK, READPAST) WHERE status = 'WAIT' ORDER BY id;-- Oracle · Tibero
SELECT id FROM job_queue WHERE status = 'WAIT' ORDER BY id FOR UPDATE SKIP LOCKED;MSSQL 은 UPDLOCK 이 행을 잠그고 READPAST 가 잠긴 행을 건너뜁니다. Oracle 은 FETCH FIRST 와 FOR UPDATE 를 함께 쓰면 오류(ORA-02014)이고, ROWNUM <= 3 은 SKIP LOCKED 보다 먼저 적용되어 3건을 못 채울 수 있습니다. 그래서 FOR UPDATE SKIP LOCKED 커서를 열고 3건만 가져옵니다(PL/SQL, 문법 검토만).