내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
10.12
14:00
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
방법 : 오라클 19c 일반 dml 시 lock 확인 테스트
오라클을 사용하면서 dml을 하게되면 lock 이 발생함
일반적인 insert, update, delete 수행 시 각각 어떤 lock이 발생하는지 확인해봄
1. insert 시 lock 테스트
2. update 시 lock 테스트
3. delete 시 lock 테스트
4. insert append, insert append_values 시 lock 테스트
세션 정보 확인(4개 세션에서 확인)
|
1
2
3
4
5
6
|
SQL> select sid from v$mystat where rownum<=1;
SID
----------
3134
(세션 1 : 3134, 세션 2 : 1997, 세션 3 : 1424, 세션 4 : 865)
|
테스트 테이블 생성
|
1
2
3
4
5
|
SQL>
drop table dmltest1 purge;
create table dmltest1 (col1 number, col2 number);
insert into dmltest1 select object_id, object_id+1 from dba_objects;
commit;
|
1. insert 시 lock 테스트
insert 실행(sid : 3134)
|
1
2
3
|
SQL> insert into dmltest1 values (1,1);
1 row created.
|
lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 983043 1558830 6 0 0 15 3 1558830
|
TM 락 LMODE 3와 TX 락 LMODE 6이 생김
다른 세션 insert 실행(sid : 1997)
|
1
2
3
|
SQL> insert into dmltest1 values (1,1);
1 row created.
|
정상적으로 insert 됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3997727 1171835 6 0 0 61 31 1171835
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 983043 1558830 6 0 0 15 3 1558830
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
다른 세션에서 update 실행(sid : 1424)
|
1
2
3
|
SQL> update dmltest1 set col1 = 1 where col1 < 1000000;
50616 rows updated.
|
정상적으로 update 됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1424 TM 2015816 0 3 0 0 30 49736 0
1424 TX 2752534 1587296 6 0 0 42 22 1587296
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3997727 1171835 6 0 0 61 31 1171835
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 983043 1558830 6 0 0 15 3 1558830
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
sid 1424 롤백
|
1
|
SQL> rollback;
|
다른 세션에서 delete 실행(sid : 865)
|
1
2
3
|
SQL> delete dmltest1 where col1 < 1000000;
50616 rows deleted.
|
정상적으로 delete됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
865 TM 2015816 0 3 0 0 30 49736 0
865 TX 65553 1242821 6 0 0 1 17 1242821
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3997727 1171835 6 0 0 61 31 1171835
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 983043 1558830 6 0 0 15 3 1558830
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
insert 시 lock 결론
insert 트렌젝션이 있는 경우 insert, update, delete 모두 가능함
2. update 시 lock 테스트
(기존 트렌젝션 모두 rollback 처리 후 진행)
|
1
|
SQL> rollback;
|
update 실행(sid : 3134)
|
1
2
3
|
SQL> update dmltest1 set col1=1 where col1 < 1000000;
50616 rows updated.
|
lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 1245200 1630085 6 0 0 19 16 1630085
|
TM 락 LMODE 3와 TX 락 LMODE 6이 생김
다른 세션 insert 실행(sid : 1997)
|
1
2
3
|
SQL> insert into dmltest1 values (1,1);
1 row created.
|
정상적으로 insert 됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 983055 1559124 6 0 0 15 15 1559124
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 1245200 1630085 6 0 0 19 16 1630085
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
다른 세션 update 실행(sid : 1424)
3134 세션이 update 하지 않은 로우 update
|
1
2
3
|
SQL> update dmltest1 set col1 = 1 where col1 > 1000000;
236344 rows updated.
|
정상적으로 update 됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1424 TM 2015816 0 3 0 0 30 49736 0
1424 TX 2949131 1646163 6 0 0 45 11 1646163
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 983055 1559124 6 0 0 15 15 1559124
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 1245200 1630085 6 0 0 19 16 1630085
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
sid 1424 롤백
|
1
|
SQL> rollback;
|
3134 세션이 update 한 동일 로우 update(sid : 1424)
|
1
2
|
SQL> update dmltest1 set col1=1 where col1 < 1000000;
(대기)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1424 TM 2015816 0 3 0 0 30 49736 0
1424 TX 1245200 1630085 0 6 0 19 16 1630085
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 983055 1559124 6 0 0 15 15 1559124
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 1245200 1630085 6 0 1 19 16 1630085
|
1424 세션의 TX lock LMODE 값이 0 이고 REQUEST가 6으로 되어있음(TX lock에 LMODE 6(exclusive)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TX lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
대기중이던 sid 1424 명령어 취소
|
1
2
3
4
|
Ctrl+c
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
|
3134 세션이 update 하지 않은 로우 delete (sid : 865)
|
1
2
3
|
SQL> delete dmltest1 where col1 > 1000000;
236344 rows deleted.
|
정상적으로 delete됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
865 TM 2015816 0 3 0 0 30 49736 0
865 TX 1703969 1291510 6 0 0 26 33 1291510
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 983055 1559124 6 0 0 15 15 1559124
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 1245200 1630085 6 0 0 19 16 1630085
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
sid 865 롤백
|
1
|
SQL> rollback;
|
3134 세션이 update 한 로우 delete (sid : 865)
|
1
2
|
SQL> delete dmltest1 where col1 < 1000000;
(대기)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
865 TM 2015816 0 3 0 0 30 49736 0
865 TX 1245200 1630085 0 6 0 19 16 1630085
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 983055 1559124 6 0 0 15 15 1559124
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 1245200 1630085 6 0 1 19 16 1630085
|
865 세션의 TX lock LMODE 값이 0 이고 REQUEST가 6으로 되어있음(TX lock에 LMODE 6(exclusive)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TX lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
update 시 lock 결론
update 트렌젝션이 있는 경우 insert는 자유롭게 가능하지만
update, delete 의 경우 현재 update중이지 않은 row 만 update나 delete가 가능함
3. delete 시 lock 테스트
(기존 트렌젝션 모두 rollback 처리 후 진행)
|
1
|
SQL> rollback;
|
delete 실행(sid : 3134)
|
1
2
3
|
SQL> delete dmltest1 where col1 < 1000000;
50616 rows deleted.
|
lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 131072 1259778 6 0 0 2 0 1259778
|
TM 락 LMODE 3와 TX 락 LMODE 6이 생김
다른 세션 insert 실행(sid : 1997)
|
1
2
3
|
SQL> insert into dmltest1 values (1,1);
1 row created.
|
정상적으로 insert 됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3932163 1193997 6 0 0 60 3 1193997
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 131072 1259778 6 0 0 2 0 1259778
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
다른 세션 update 실행(sid : 1424)
3134 세션이 delete 하지 않은 로우 update
|
1
2
3
|
SQL> update dmltest1 set col1 = 1 where col1 > 1000000;
236344 rows updated.
|
정상적으로 update 됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1424 TM 2015816 0 3 0 0 30 49736 0
1424 TX 262166 1305462 6 0 0 4 22 1305462
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3932163 1193997 6 0 0 60 3 1193997
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 131072 1259778 6 0 0 2 0 1259778
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
sid 1424 롤백
|
1
|
SQL> rollback;
|
3134 세션이 delete 한 동일 로우 update(sid : 1424)
|
1
2
|
SQL> update dmltest1 set col1=1 where col1 < 1000000;
(대기)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1424 TM 2015816 0 3 0 0 30 49736 0
1424 TX 131072 1259778 0 6 0 2 0 1259778
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3932163 1193997 6 0 0 60 3 1193997
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 131072 1259778 6 0 1 2 0 1259778
|
1424 세션의 TX lock LMODE 값이 0 이고 REQUEST가 6으로 되어있음(TX lock에 LMODE 6(exclusive)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TX lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
대기중이던 sid 1424 명령어 취소
|
1
2
3
4
|
Ctrl+c
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
|
3134 세션이 delete 하지 않은 로우 delete (sid : 865)
|
1
2
3
|
SQL> delete dmltest1 where col1 > 1000000;
236344 rows deleted.
|
정상적으로 delete됨
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
865 TM 2015816 0 3 0 0 30 49736 0
865 TX 4325399 1043092 6 0 0 66 23 1043092
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3932163 1193997 6 0 0 60 3 1193997
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 131072 1259778 6 0 0 2 0 1259778
|
동일한 TM 락 LMODE 3와 TX 락 LMODE 6이 하나더 생김
sid 865 롤백
|
1
|
SQL> rollback;
|
3134 세션이 delete 한 로우 delete (sid : 865)
|
1
2
|
SQL> delete dmltest1 where col1 < 1000000;
(대기)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
865 TM 2015816 0 3 0 0 30 49736 0
865 TX 131072 1259778 0 6 0 2 0 1259778
1997 TM 2015816 0 3 0 0 30 49736 0
1997 TX 3932163 1193997 6 0 0 60 3 1193997
3134 TM 2015816 0 3 0 0 30 49736 0
3134 TX 131072 1259778 6 0 1 2 0 1259778
|
865 세션의 TX lock LMODE 값이 0 이고 REQUEST가 6으로 되어있음(TX lock에 LMODE 6(exclusive)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TX lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
delete 시 lock 결론
delete 트렌젝션이 있는 경우 insert는 자유롭게 가능하지만
update, delete 의 경우 현재 delete중이지 않은 row 만 update나 delete가 가능함
추가 insert append, insert append_values 시 lock 테스트
(기존 트렌젝션 모두 rollback 처리 후 진행)
|
1
|
SQL> rollback;
|
insert append 실행(sid : 3134)
|
1
|
SQL> insert /*+ append */ into dmltest1 select object_id, object_id+1 from dba_objects;
|
lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
3134 TM 2015816 0 6 0 0 30 49736 0
3134 TX 4587547 646456 6 0 0 70 27 646456
|
3134 세션의 TM,TX lock LMODE 값이 모두 6임
현재 세션에서 insert 실행
|
1
2
3
4
|
SQL> insert into dmltest1 values (1, 1);
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
에러 발생하면서 안됨
다른 세션에서 insert 실행(sid : 1997)
|
1
2
|
SQL> insert into dmltest1 values (1, 1);
(대기)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2015816 0 0 3 0 30 49736 0
3134 TM 2015816 0 6 0 1 30 49736 0
3134 TX 4587547 646456 6 0 0 70 27 646456
|
1997 세션의 TM lock LMODE 값이 0 이고 REQUEST가 3으로 되어있음(TM lock에 LMODE 3(row-X)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TM lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
1997 세션 insert 취소 후 update 시도
|
1
2
|
SQL> update dmltest1 set col1 = 1 where col1 > 1000000;
(대기)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2015816 0 0 3 0 30 49736 0
3134 TM 2015816 0 6 0 1 30 49736 0
3134 TX 4587547 646456 6 0 0 70 27 646456
|
insert 시와 동일한 모습
1997 세션의 TM lock LMODE 값이 0 이고 REQUEST가 3으로 되어있음(TM lock에 LMODE 3(row-X)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TM lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
1997 세션 update 취소 후 delete 시도
|
1
2
|
SQL> delete dmltest1 where col1 > 1000000;
(대기)
|
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2015816 0 0 3 0 30 49736 0
3134 TM 2015816 0 6 0 1 30 49736 0
3134 TX 4587547 646456 6 0 0 70 27 646456
|
insert 시와 동일한 모습
1997 세션의 TM lock LMODE 값이 0 이고 REQUEST가 3으로 되어있음(TM lock에 LMODE 3(row-X)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TM lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
3134, 1997 세션 모두 취소 및 rollback
|
1
|
SQL> rollback;
|
insert append_values 실행
|
1
|
SQL> insert /*+ append_values */ into dmltest1 values (1, 1);
|
lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
3134 TM 2015816 0 6 0 0 30 49736 0
3134 TX 4325400 1043338 6 0 0 66 24 1043338
|
3134 세션의 TM,TX lock LMODE 값이 모두 6임
현재 세션에서 insert 실행
|
1
2
3
4
|
SQL> insert into dmltest1 values (1, 1);
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
에러 발생하면서 안됨
다른 세션에서 insert, update, delete 실행(sid : 1997)
|
1
2
3
4
5
6
|
SQL>
insert into dmltest1 values (1, 1);(대기)
쿼리 취소
update dmltest1 set col1 = 1 where col1 > 1000000;(대기)
쿼리 취소
delete dmltest1 where col1 > 1000000;(대기)
|
각각 모두 대기함
lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (3134, 1997, 1424, 865)
and TYPE in ('TX','TM')
order by sid, type
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1997 TM 2015816 0 0 3 0 30 49736 0
3134 TM 2015816 0 6 0 1 30 49736 0
3134 TX 4325400 1043338 6 0 0 66 24 1043338
|
1997 세션의 TM lock LMODE 값이 0 이고 REQUEST가 3으로 되어있음(TM lock에 LMODE 3(row-X)을 걸기 위해 대기중이라는 뜻)
3134 세션의 TM lock BLOCK 값이 1 임(현재 lock 이 어떤 세션을 block 하고 있다는 뜻)
insert append, insert append_values 시 lock 결론
append, append_values 힌트를 사용해 insert 시 TM, TX 모두 LMODE 6(exclusive)로 걸려
본인 세션 포함 어떤 세션도 해당 테이블에 dml 을 할수 없음
참조 :
https://positivemh.tistory.com/892
오라클 19c 파티션 테이블 append insert 시 lock 확인
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 파티션 테이블 append insert 시 lock 확인 만약 파티션 테이블에 일반 테이블의 데이터를 통으로 밀어 넣어야할 때 일반 inser
positivemh.tistory.com
https://positivemh.tistory.com/903
오라클 19c Online index 생성시 lock 확인 테스트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c online index 생성시 lock 확인 테스트 인덱스 생성 시 dml이 없는 업무 외 시간인 경우 그냥 인덱스를 생성할수도 있지만 업
positivemh.tistory.com
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-LOCK.html#GUID-87D76889-832C-4BFC-B8B0-154A22721781
http://wiki.gurubee.net/display/STUDY/1.Lock
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 소문자 컬럼 테스트 (0) | 2023.06.06 |
|---|---|
| 오라클 19c insert 쿼리 logical read 확인 (0) | 2023.06.02 |
| 오라클 19c ASM usable_file_mb -(음수) 됬을때 조치방법 (0) | 2023.05.23 |
| 오라클 19c 파티션 Exchange 방법 (0) | 2023.05.03 |
| 오라클 19c 파티션 테이블 append insert 시 lock 확인 (0) | 2023.05.01 |