복사되었습니다!
OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle AI Database 23.26.2.0.0 ai
방법 : 오라클 26ai deadlock 자동 테스트 스크립트
일반적으로 deadlock을 발생시키려면 아래 글처럼 테이블을 생성하고 세션1, 세션2에서 각각 교차 dml을 수행하여야함
참고 : deadlock 발생 테스트 ORA-00060 ( https://positivemh.tistory.com/317 )
본문에서는 이 테스트를 스크립트 하나로 할수 있게끔 만든 스크립트를 설명하고 실제 수행해봄
참고로 현재 db 환경은 싱글 db 환경임
테스트
쉘스크립트 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
|
$ vi deadlock_test.sh
#!/bin/bash
set -uo pipefail
DB_CONNECT="${DB_CONNECT:-${1:-}}"
TABLE_NAME="${TABLE_NAME:-TEST_DEADLOCK}"
RUN_TIME=$(date '+%Y%m%d_%H%M%S')
OUTPUT_DIR="./deadlock_test_${RUN_TIME}"
TEMP_DIR=$(mktemp -d)
PID1=""
PID2=""
usage()
{
echo "사용법"
echo " $0 '사용자/비밀번호@서비스명'"
echo
echo "예시"
echo " $0 'testuser/testpass@ORCL'"
echo
echo "환경 변수 사용"
echo " DB_CONNECT='testuser/testpass@ORCL' $0"
}
if [ -z "$DB_CONNECT" ]; then
usage
exit 1
fi
if ! command -v sqlplus >/dev/null 2>&1; then
echo "sqlplus 명령을 찾을 수 없음"
exit 1
fi
case "$TABLE_NAME" in
''|*[!A-Za-z0-9_]*)
echo "테이블명에는 영문, 숫자, 밑줄만 사용할 수 있음"
exit 1
;;
esac
mkdir -p "$OUTPUT_DIR"
cleanup()
{
if [ -n "$PID1" ] && kill -0 "$PID1" 2>/dev/null; then
kill "$PID1" 2>/dev/null || true
fi
if [ -n "$PID2" ] && kill -0 "$PID2" 2>/dev/null; then
kill "$PID2" 2>/dev/null || true
fi
sqlplus -s "$DB_CONNECT" > "$OUTPUT_DIR/cleanup.log" 2>&1 <<EOF
set echo off
set feedback off
whenever sqlerror continue
begin
execute immediate 'drop table ${TABLE_NAME} purge';
exception
when others then
if sqlcode != -942 then
raise;
end if;
end;
/
exit
EOF
rm -rf "$TEMP_DIR"
}
trap cleanup EXIT INT TERM
cat > "$TEMP_DIR/setup.sql" <<EOF
set echo on
set feedback on
set serveroutput on
whenever sqlerror exit failure rollback
begin
execute immediate 'drop table ${TABLE_NAME} purge';
exception
when others then
if sqlcode != -942 then
raise;
end if;
end;
/
create table ${TABLE_NAME}
(
id number primary key,
val number
);
insert into ${TABLE_NAME} values (1, 0);
insert into ${TABLE_NAME} values (2, 0);
commit;
select *
from ${TABLE_NAME}
order by id;
exit
EOF
cat > "$TEMP_DIR/session1.sql" <<EOF
set echo on
set feedback on
set timing on
set serveroutput on
alter session set tracefile_identifier = 'DEADLOCK_TEST_S1';
begin
dbms_application_info.set_module(
module_name => 'DEADLOCK_TEST',
action_name => 'SESSION_1'
);
end;
/
prompt
prompt ==========================================
prompt SESSION 1 : ID 1 행 잠금
prompt ==========================================
update ${TABLE_NAME}
set val = val + 1
where id = 1;
prompt SESSION 1 : ID 1 행 잠금 완료
prompt SESSION 1 : 5초 대기
begin
dbms_session.sleep(5);
end;
/
prompt
prompt SESSION 1 : ID 2 행 잠금 시도
prompt SESSION 1 : SESSION 2가 보유한 잠금을 대기함
prompt
update ${TABLE_NAME}
set val = val + 1
where id = 2;
prompt
prompt SESSION 1 : ROLLBACK 수행
prompt
rollback;
exit
EOF
cat > "$TEMP_DIR/session2.sql" <<EOF
set echo on
set feedback on
set timing on
set serveroutput on
alter session set tracefile_identifier = 'DEADLOCK_TEST_S2';
begin
dbms_application_info.set_module(
module_name => 'DEADLOCK_TEST',
action_name => 'SESSION_2'
);
end;
/
prompt SESSION 2 : SESSION 1이 먼저 행을 잠그도록 1초 대기
begin
dbms_session.sleep(1);
end;
/
prompt
prompt ==========================================
prompt SESSION 2 : ID 2 행 잠금
prompt ==========================================
update ${TABLE_NAME}
set val = val + 1
where id = 2;
prompt SESSION 2 : ID 2 행 잠금 완료
prompt SESSION 2 : 5초 대기
begin
dbms_session.sleep(5);
end;
/
prompt
prompt SESSION 2 : ID 1 행 잠금 시도
prompt SESSION 2 : 데드락 발생
prompt
update ${TABLE_NAME}
set val = val + 1
where id = 1;
prompt
prompt SESSION 2 : ROLLBACK 수행
prompt
rollback;
exit
EOF
echo "테스트 테이블 생성"
sqlplus -s "$DB_CONNECT" @"$TEMP_DIR/setup.sql" \
> "$OUTPUT_DIR/setup.log" 2>&1
SETUP_RESULT=$?
if [ "$SETUP_RESULT" -ne 0 ]; then
echo "테스트 테이블 생성 실패"
echo "로그 확인 : $OUTPUT_DIR/setup.log"
exit 1
fi
echo "SESSION 1 실행"
sqlplus -s "$DB_CONNECT" @"$TEMP_DIR/session1.sql" \
> "$OUTPUT_DIR/session1.log" 2>&1 &
PID1=$!
echo "SESSION 2 실행"
sqlplus -s "$DB_CONNECT" @"$TEMP_DIR/session2.sql" \
> "$OUTPUT_DIR/session2.log" 2>&1 &
PID2=$!
wait "$PID1"
SESSION1_RESULT=$?
wait "$PID2"
SESSION2_RESULT=$?
PID1=""
PID2=""
echo
echo "=========================================="
echo "데드락 테스트 결과"
echo "=========================================="
if grep -q "ORA-00060" "$OUTPUT_DIR/session1.log" "$OUTPUT_DIR/session2.log"; then
echo "ORA-00060 발생 확인"
echo
grep -H -B 2 -A 3 "ORA-00060" \
"$OUTPUT_DIR/session1.log" \
"$OUTPUT_DIR/session2.log"
else
echo "ORA-00060이 확인되지 않음"
echo "세션 로그를 확인해야 함"
fi
echo
echo "로그 경로"
echo " $OUTPUT_DIR/setup.log"
echo " $OUTPUT_DIR/session1.log"
echo " $OUTPUT_DIR/session2.log"
echo " $OUTPUT_DIR/cleanup.log"
echo
echo "테스트 종료"
|
스크립트 설명 :
사용방법 : sh deadlock.sh 사용자/비밀번호@서비스명 방식으로 수행여야함
동작 원리 :
스크립트를 실행하면 접속 정보로 데이터베이스에 접속한 뒤 TEST_DEADLOCK 테이블을 생성함
테이블에는 아래와 같이 두 건의 테스트 데이터를 입력함(ID 1, ID 2)
이후 두 개의 SQL*Plus 세션을 백그라운드로 실행함
SESSION 1은 ID 1 행을 먼저 UPDATE하여 잠금을 획득함
SESSION 2는 ID 2 행을 UPDATE하여 잠금을 획득함
각 세션은 잠금을 유지한 상태에서 5초 동안 대기함
대기 이후 SESSION 1은 SESSION 2가 잠근 ID 2 행의 UPDATE를 시도함
SESSION 2는 SESSION 1이 잠근 ID 1 행의 UPDATE를 시도함
두 세션이 서로 상대방의 잠금 해제를 기다리는 상태가 되면서 Deadlock이 발생함
Oracle은 Deadlock을 감지한 뒤 두 세션 중 하나에 ORA-00060 오류를 반환함
오류가 발생하는 세션은 실행 상황에 따라 달라질 수 있으므로 스크립트에서는 session1.log와 session2.log를 모두 확인함
스크립트에 권한 부여
|
1
|
$ chmod +x deadlock_test.sh
|
스크립트 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
$ sh deadlock.sh eric/eric@ORACLE26PDB1
테스트 테이블 생성
SESSION 1 실행
SESSION 2 실행
==========================================
데드락 테스트 결과
==========================================
ORA-00060 발생 확인
./deadlock_test_20260728_083720/session1.log- *
./deadlock_test_20260728_083720/session1.log-ERROR at line 2:
./deadlock_test_20260728_083720/session1.log:ORA-00060: deadlock detected while waiting for resource
./deadlock_test_20260728_083720/session1.log-Help: https://docs.oracle.com/error-help/db/ora-00060/
./deadlock_test_20260728_083720/session1.log-
./deadlock_test_20260728_083720/session1.log-
로그 경로
./deadlock_test_20260728_083720/setup.log
./deadlock_test_20260728_083720/session1.log
./deadlock_test_20260728_083720/session2.log
./deadlock_test_20260728_083720/cleanup.log
테스트 종료
|
정상적으로 테스트가 완료됨
alert log 확인
|
1
2
3
4
5
6
|
$ tail -300f /app/oracle/diag/rdbms/oracle26/ORACLE26/trace/alert_ORACLE26.log
...
2026-07-28T07:30:48.315521+09:00
ORACLE26PDB1(3):Errors in file /app/oracle/diag/rdbms/oracle26/ORACLE26/trace/ORACLE26_ora_1501177_DEADLOCK_TEST_S1.trc:
2026-07-28T07:30:49.735931+09:00
ORACLE26PDB1(3):ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /app/oracle/diag/rdbms/oracle26/ORACLE26/trace/ORACLE26_ora_1501177_DEADLOCK_TEST_S1.trc.
|
alert log에 deadlock 이벤트가 발생함
멀티테넌트 환경이라서 pdb이름과 함께 ORA 메세지가 나옴
논cdb 환경이면 아래와 같이 에러가 발생함
|
1
2
3
4
5
|
$ tail -300f /app/oracle/diag/rdbms/oracle19/ORACLE19/trace/alert_ORACLE19.log
2026-07-28T07:51:55.506764+09:00
Errors in file /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_2863810_DEADLOCK_TEST_S1.trc:
2026-07-28T07:51:55.861872+09:00
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_2863810_DEADLOCK_TEST_S1.trc.
|
결론 :
빠르게 deadlock 에러(ORA-00060)을 발생시키고 싶은 경우 본문 스크립트를 사용하면됨
참조 :
deadlock 발생 테스트 ORA-00060 ( https://positivemh.tistory.com/317 )
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-C1971E9B-849A-4634-9575-4F8FAD697750
'ORACLE > Sql' 카테고리의 다른 글
| sqlplus에서 바인드 변수 사용법 (0) | 2026.04.01 |
|---|---|
| 오라클 19c sql로 크리스마스 트리 만들기 (0) | 2025.12.24 |
| 오라클 19c redo 많이 발생한 시점 확인 (0) | 2025.11.29 |
| 오라클 19c 리스너 로그 집계 스크립트 (0) | 2025.11.11 |
| 오라클 19c Hugepages 설정 스크립트 최신버전 및 수동계산 (1) | 2025.09.28 |