Lock 살펴보기

Suresh Ramagiri -

데이터베이스에서 잠금의 가장 일반적인 원인은 동시 트랜잭션이 테이블의 동일한 행을 업데이트하려고 하는 경우입니다. MVCC의 일부로 잠금은 서로 다른 트랜잭션에서 데이터 일관성을 유지해야합니다. 잠금은 응용 프로그램에 대기 시간으로 나타나므로 잠금이 걸려 유지되는 이유를 식별하고 사용자 환경에서 잠금 경합의 영향을 줄이거나 없애는 것이 중요합니다 

system에서 활성 잠금을 찾아보려면 다음 쿼리를 사용할 수 있습니다.

sql> select * from system.lockman; 

예:

sql> select * from system.lockman;
+--------+---------------------+---------------------+---------+------------+--------+---------------------+-----------+
| nodeid | holder | waiter | wait_ms | timeout_ms | domain | key | exclusive |
+--------+---------------------+---------------------+---------+------------+--------+---------------------+-----------+
| 15 | 6268069961036267554 | 6268069963849242654 | 11977 | 0 | 4103 | 5979715258906695681 | 1 |
| 15 | 6268069961036267554 | 6268069968114098208 | 10980 | 0 | 4103 | 5979715258906695681 | 1 |
| 15 | 6268069961036267554 | 6268069975814778910 | 9249 | 0 | 4103 | 5979715258906695681 | 1
+--------+---------------------+---------------------+---------+------------+---------------------+---------------------+-----------+--------+------------------+---------------+
1 row in set (0.01 sec)
 

holder는 잠금을 보유하고 있는 트랜잭션의 XID이고 waiter는 잠금이 해제되기를 기다리는 XID를 나타냅니다. 이 예는 11초 이상 잠금 상태에 있는 일부 트랜잭션을 명확히 보여줍니다 (커밋되지 않은 명시적 쓰기 트랜잭션이었습니다).

주어진 시간에 가장 많은 잠금을 보유한 트랜잭션을 보려면 다음 쿼리를 사용할 수 있습니다.

SQL> SELECT * FROM system.sessions WHERE xid = (select holder FROM (select holder,count(1) cnt FROM system.lockman GROUP BY holder ORDER BY cnt desc limit 1) dummy)\G

아래 예제는 커밋되기를 기다리는 명시적 쓰기 트랜잭션입니다.

sql> SELECT * FROM system.sessions WHERE xid = (select holder FROM (select holder,count(1) cnt FROM system.lockman GROUP BY holder ORDER BY cnt desc limit 1) dummy)\G
*************************** 1. row ***************************
nodeid: 4
session_id: 3774276612
source_ip: 10.65.148.79
source_port: 33845
local_ip: 10.65.60.51
local_port: 3306
user: 5762026690960054275
database: foo
trx_state: open
statement_state: finished
xid: 6268069961036267554
cpu: 1
isolation: REPEATABLE-READ
last_statement: update SOME_TABLE set product_version = 25 where product_id = 'ABC123' and supplier_id ='foobar_id' and sku_id ='SKUABC123' and product_version = 24
time_in_state_s: 18715
created: 2016-07-11 09:27:16
heap_id: 72057594040818613
trx_age: 0
trx_mode: explicit
trx_counter_select: 0
trx_counter_insert: 0
trx_counter_update: 0
trx_counter_delete: 0
trx_is_writer: 0
1 row in set (0.01 sec)

 

잠금 유형

직렬화된 잠금 대기(Serialized Locking Queue):

위 쿼리 결과가 계속 변경되면 이는 대개 직렬화된 잠금 대기를 나타냅니다. 이런 잠금 유형의 가장 일반적인 원인은 삽입 또는 업데이트되는 행이 겹치는 것입니다. 예를 들어:

  • 쿼리 A는 고유 id 1,2,3을 가진 행을 삽입합니다
  • 쿼리 B는 고유 id 2,4를 가진 행을 업데이트합니다
  • 쿼리 C는 고유 id 4,1을 가진 행을 업데이트 합니다

쿼리 B는 쿼리 A가 잠근 행에 영향을 미치므로 쿼리 A가 변경 내용을 적용 할 때까지 기다려야합니다. 다음으로 쿼리 C는 동일한 행을 업데이트하므로 쿼리 B가 완료 될 때까지 기다려야합니다. 이렇게 긴 이벤트는 체인의 마지막에서 쿼리를 오래 걸리게 하고 결과적으로 결과적으로 lockman_waittime_ms에 값을 커지게 만듭니다 (아래 잠금 이력 참조).

직렬화 잠금 대기를 해결 또는 회피하는 방법:

  • 동일 행에 대한 동시 업데이트를 피하십시오.
  • 잠금을 발생하는 쿼리의 성능을 향상시키기 위한 방안을 찾아보십시오 (인덱스 및 쿼리 튜닝).

나쁜 쿼리 / 누락된 인덱스:

잘못 작성된 쿼리나 인덱스가 누락된 쿼리로 인해 때로 데이터베이스에 문제를 발생할 수 있습니다. 중복되는 쓰기 쿼리는 해당 행이나 테이블 대기열에 대한 다른 쿼리를 잠금으로 설정하여 연속적인 문제를 일으킬 수 있습니다.

쿼리에 인덱스가 누락되었거나 성능이 좋지 않은지 여부를 식별하고 테이블의 Explain 계획과 DDL을 검토하여 플랜 비용을 이해하고 인덱스를 추가하거나 쿼리를 다시 작성하는 것이 성능 향상에 유용합니다.

잠금 이력:

과거에 잠금에 의해 보류 된 쿼리를 확인하기 위해 쿼리 로그, 특히`lockman_waits`와`lockman_waittime_ms`를 볼 수 있습니다. 다수의 lockman_waits를 가진 높은 lockman_waittime_ms 시간은 일반적으로 직렬화된 잠금 대기열을 나타내지만 단일 (또는 매우 적은 양) lockman_waits를 가진 lockman_waittime_ms는 로깅된 쿼리가 쓰기를 시도하려고 했던 큰 테이블 또는 행 잠금을 획득했음을 나타냅니다. 때로 기다리는 쿼리와 같은 시간에 로깅된 쿼리에 잠금을 보유하고 있음을 찾을 수 있습니다. 주어진 로그 세그먼트에 대해 lockman_waittime이 가장 많은 쿼리를 다음과 같이 볼 수 있습니다.

[root@test001 ~]# /opt/clustrix/bin/clx cmd 'grep "^YYYY-MM-DD" /data/clustrix/log/query.log' > /tmp/YYYY-MM-DD_query.log
[root@test001 ~]# cat /tmp/YYYY-MM-DD_query.log | sed 's/^.*lockman_waittime_ms: \([0-9]*\).*$/\1 \0/' |sort -rn|less

예 ("lockman_waitime_ms"를 기준으로 정렬된 쿼리 로그 항목):

2016-03-22 21:40:05.449257 UTC ip-10-10-10-1 clxnode: INSTR SQLERR SID:149052431 db=test user=test@10.10.10.1 ac=N xid=56fca1e77ece381e sql="INSERT INTO `foo` (`account_id`, `created_at`, `member_id`, `sub_list_id`, `updated_at`) VALUES (7825, '2016-03-22 21:31:25', 78348504, 166562, '2016-03-22 21:31:25')" [Session killed due to closed client socket: ] time 520100.0ms; reads: 6; inserts: 15; deletes: 0; updates: 0; counts: 1; rows_read: 6; forwards: 13; broadcasts: 0; rows_output: 7; semaphore_matches: 0; fragment_executions: 15; runtime_ns: 13; cpu_waits: 1; cpu_waittime_ns: 1; bm_fixes: 39; bm_loads: 0; bm_waittime_ns: 0; lockman_waits: 2; lockman_waittime_ms: 517712; trxstate_waits: 0; trxstate_waittime_ms: 0; wal_perm_waittime_ms: 0; bm_perm_waittime_ms: 0;attempts: 2

2016-03-22 21:41:18.389570 UTC ip-1010-10-2 clxnode: INSTR SLOW SID:231803921 db=test user=test@10.10.10.2 ac=N xid=56fca211d2b06022 sql="INSERT INTO `foo` (`account_id`, `created_at`, `member_id`, `sub_list_id`, `updated_at`) VALUES (7825, '2016-03-22 21:33:51', 78348504, 84758, '2016-03-22 21:33:51')" [Ok: 1 rows affected] time 446739.8ms; reads: 3; inserts: 13; deletes: 0; updates: 0; counts: 1; rows_read: 3; forwards: 12; broadcasts: 0; rows_output: 2; semaphore_matches: 0; fragment_executions: 13; runtime_ns: 11; cpu_waits: 0; cpu_waittime_ns: 0; bm_fixes: 44; bm_loads: 0; bm_waittime_ns: 0; lockman_waits: 1; lockman_waittime_ms: 446699; trxstate_waits: 0; trxstate_waittime_ms: 0; wal_perm_waittime_ms: 0; bm_perm_waittime_ms: 0; attempts: 1

2016-03-22 21:33:09.726210 UTC ip-10-10-10-1 clxnode: INSTR SLOW SID:149049359 db=test user=test@10.10.10.1 ac=N xid=56fca1791d45d81e sql="INSERT INTO `foo` (`account_id`, `created_at`, `member_id`, `sub_list_id`, `updated_at`) VALUES (7825, '2016-03-22 21:31:20', 78348504, 166562, '2016-03-22 21:31:20')" [Ok: 1 rows affected] time 109261.8ms; reads: 3; inserts: 13; deletes: 0; updates: 0; counts: 1; rows_read: 3; forwards: 12; broadcasts: 0; rows_output: 2; semaphore_matches: 0; fragment_executions: 13; runtime_ns: 11; cpu_waits: 0; cpu_waittime_ns: 0; bm_fixes: 47; bm_loads: 0; bm_waittime_ns: 0; lockman_waits: 1; lockman_waittime_ms: 109232; trxstate_waits: 0; trxstate_waittime_ms: 0; wal_perm_waittime_ms: 0; bm_perm_waittime_ms: 0; attempts: 1

위 예제 로그에서 가장 긴 대기자(waiter)의 경우, 두개의 서로 다른 잠금을 총 520100.0ms (520초) 동안 기다리고 있었고 자동 트랜잭션 종결자(killer)에 의해 종료되었습니다. 추측건데, 클라이언트 net_write_timeout은 500초로 설정됩니다. 위의 쿼리는 runtime_ns (13) 동안 매우 작은 값을 가지고 있으며 이는 대부분의 시간을 잠금 대기로 보냈음을 의미합니다.

잠금이 이미 식별된 쿼리에 의해 발생한다고 가정하면 식별된 삽입/업데이트는 직접 영향을 미치는 행만 잠가야 합니다. 쿼리가 잠금으로 겹치는 부분이 있습니다. 30초 이상 걸리는 몇 개의 쿼리가 서로 완료되기를 기다리면 시간이 계속 쌓인다는 것을 상상할 수 있을 것입니다. 이전 예에서, 다음 결과를 얻게 됩니다.

  • Query A - 쿼리 A 만큼 시간이 걸립니다
  • Query B - 쿼리 A & B 만큼 시간이 걸립니다
  • Query C – 쿼리 A & B & C 만큼 시간이 걸립니다

오버랩이 많을 수록 대기가 오래 걸리고 결과는 더 나빠집니다. 오버랩을 줄이거나 개별 쿼리의 실행시간을 줄일 수 있다면, 전반적으로 잠금이 줄어들게 됩니다. 특히 오버랩이 발생될 것으로 예상되는 경우 이것을 배치로 실행하는 것이 이슈를 줄이는데 도움이 됩니다.

또 다른 질문이 있으십니까? 문의 등록

0 댓글

댓글을 남기려면 로그인하세요.
Zendesk 제공