ロックモード

(8.0)

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/data-locks-table.html
https://dev.mysql.com/doc/refman/8.0/en/metadata-locks-table.html

 


-- ロックリソースタイプ
行ロック
表ロック
メタデータロック


-- ロック種類(行ロック、表ロック)

S(shared)
X(exclusive)
IS(intention shared)
IX(intention exclusive)
S_GAP(shared gap)
X_GAP(exclusive gap)
IS_GAP(intention shared gap)
IX_GAP(intention exclusive gap)
AUTO_INC(auto increment)

-- ロック種類(メタデータロック)

INTENTION_EXCLUSIVE
SHARED
SHARED_HIGH_PRIO
SHARED_READ
SHARED_WRITE
SHARED_UPGRADABLE
SHARED_NO_WRITE
SHARED_NO_READ_WRITE
EXCLUSIVE
SHARED_READ_ONLY


-- ロック確認

1.テーブル準備
drop table tab1;
create table tab1(col1 int not null,col2 int);
alter table tab1 add constraint tab1pk primary key(col1);
insert into tab1 values(1,1);
select * from tab1;


2.セッションID確認

select connection_id();


3.確認SQL


select * from performance_schema.data_locks\G
select * from performance_schema.data_lock_waits\G
select * from performance_schema.metadata_locks\G
select * from performance_schema.metadata_locks where object_name = 'tab1'\G

select * from performance_schema.threads\G
show processlist;
select * from information_schema.INNODB_TRX order by trx_id\G

 

-- ロック確認結果
メタデータロックと表ロックのみ記載


select * from tab1 where col1 = 1;
→ SHARED_READ


select * from tab1 where col1 = 1 for update;
→ SHARED_WRITE
→ 表ロック:IX

insert into tab1 values(2,2);
→ SHARED_WRITE
→ 表ロック:IX

update tab1 set col2 = 2 where col1 = 1;
→ SHARED_WRITE
→ 表ロック:IX

delete from tab1 where col1 = 1;
→ SHARED_WRITE
→ 表ロック:IX

 

lock table tab1 read;
→ SHARED_READ_ONLY

lock table tab1 write;
→ SHARED_NO_READ_WRITE


create index ind12 on tab1(col2);
→ SHARED_UPGRADABLE

drop index ind12 on tab1;
→ SHARED_UPGRADABLE

alter table tab1 add col3 int;
→ SHARED_UPGRADABLE

alter table tab1 drop col3 ;
→ SHARED_UPGRADABLE


analyze table tab1;
→ SHARED_READ

 

 

(12cR1)

https://docs.oracle.com/cd/E57425_01/121/SQLRF/ap_locks001.htm
https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-87D76889-832C-4BFC-B8B0-154A22721781.htm

 


-- ロックリソースタイプ
行ロック(TX)
表ロック(TM)

-- ロック種類
1. なし
2. RS(行共有) 別名SS
3. RX(行排他) 別名SX
4. S(共有)
5. SRX(共有行排他) 別名SSX
6. X(排他)


-- ロック確認

1.テーブル準備
drop table tab1 purge;
create table tab1(col1 int not null,col2 int);
create unique index ind11 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1) using index ind11;
insert into tab1 values(1,1);
commit;
select * from tab1;


2.セッションID確認

select sys_context('userenv','sid') from dual;

3.確認SQL

alter session set ddl_lock_timeout = 3600;


define def1 = 440;
select sid,type,lmode,request,ctime from v$lock where type in ('TX','TM') and sid = &def1;
select sql_text from v$sql where sql_id in ( select prev_sql_id from v$session where sid = &def1 );


-- ロック確認結果


select * from tab1 where col1 = 1;
→ ロックなし

select * from tab1 where col1 = 1 for update;
→ 行ロック:6.排他(X) + 表ロック:3.RX(行排他)

insert into tab1 values(2,2);
→ 行ロック:6.排他(X) + 行ロック:4.S(共有) + 表ロック:3.RX(行排他)

update tab1 set col2 = 2 where col1 = 1;
→ 行ロック:6.排他(X) + 表ロック:3.RX(行排他)

delete from tab1 where col1 = 1;
→ 行ロック:6.排他(X) + 表ロック:3.RX(行排他)

 

lock table tab1 in exclusive mode;
→ 表ロック:6.排他(X)

lock table tab1 in share row exclusive mode;
→ 表ロック:5.SRX(共有行排他)

lock table tab1 in share mode;
→ 表ロック:4.S(共有)

lock table tab1 in row exclusive mode;
→ 表ロック:3.RX(行排他)

lock table tab1 in row share mode;
→ 表ロック:2.RS(行共有)

 

create index ind12 on tab1(col2) online;
→ 表ロック:2.RS(行共有)

drop index ind12 online;
→ 表ロック:2.RS(行共有)


alter table tab1 add(col3 int);
→ 表ロック:6.排他(X)

alter table tab1 drop(col3);
→ 表ロック:6.排他(X)

exec dbms_stats.gather_table_stats('TEST','TAB1');
→ ロックなし

 

(9.4)

https://www.postgresql.jp/document/9.4/html/explicit-locking.html
https://www.postgresql.jp/document/9.4/html/view-pg-locks.html


-- ロックリソースタイプ
行レベル
表レベル


-- 行ロック種類
1. FOR KEY SHARE
2. FOR SHARE
3. FOR NO KEY UPDATE
4. FOR UPDATE


-- 表ロック種類
1. ACCESS SHARE
2. ROW SHARE
3. ROW EXCLUSIVE
4. SHARE UPDATE EXCLUSIVE
5. SHARE
6. SHARE ROW EXCLUSIVE
7. EXCLUSIVE
8. ACCESS EXCLUSIVE

 

-- ロック確認

1.テーブル準備
drop table tab1;
create table tab1(col1 int not null,col2 int);
create unique index ind11 on tab1(col1);
alter table tab1 add constraint tab1pk primary key using index ind11;
insert into tab1 values(1,1);
select * from tab1;


2.セッションID確認

select pg_backend_pid();

3.確認SQL


select l.pid, db.datname, c.relname, l.locktype, l.mode, l.granted, l.transactionid, l.pid
from pg_locks l
left join pg_class c on l.relation = c.oid
left join pg_database db on l.database = db.oid
order by l.pid;


create extension pgrowlocks;

select * from tab1 as a, pgrowlocks('tab1') as p
where p.locked_row = a.ctid;

 

-- ロック確認結果

select * from tab1 where col1 = 1;
→ 表ロック:1.ACCESS SHARE

select * from tab1 where col1 = 1 for update;
→ 行ロック:4.FOR UPDATE + 表ロック:2.ROW SHARE

insert into tab1 values(2,2);
→ 表ロック:3.ROW EXCLUSIVE

update tab1 set col2 = 2 where col1 = 1;
→ 行ロック:3.FOR NO KEY UPDATE + 表ロック:3.ROW EXCLUSIVE

delete from tab1 where col1 = 1;
→ 行ロック:4.FOR UPDATE + 表ロック:3.ROW EXCLUSIVE

 

lock table tab1 in access share mode;
→ 表ロック:1.ACCESS SHARE

lock table tab1 in row share mode;
→ 表ロック:2.ROW SHARE

lock table tab1 in row exclusive mode;
→ 表ロック:3.ROW EXCLUSIVE

lock table tab1 in share update exclusive mode;
→ 表ロック:4.SHARE UPDATE EXCLUSIVE

lock table tab1 in share mode;
→ 表ロック:5.SHARE

lock table tab1 in share row exclusive mode;
→ 表ロック:6.SHARE ROW EXCLUSIVE

lock table tab1 in exclusive mode;
→ 表ロック:7.EXCLUSIVE

lock table tab1 in access exclusive mode;
→ 表ロック:8.ACCESS EXCLUSIVE


create index concurrently ind12 on tab1(col2);
→ 表ロック:4.SHARE UPDATE EXCLUSIVE

drop index concurrently ind12;
→ 表ロック:4.SHARE UPDATE EXCLUSIVE

alter table tab1 add col3 int;
→ 表ロック:8.ACCESS EXCLUSIVE

alter table tab1 drop col3;
→ 表ロック:8.ACCESS EXCLUSIVE

analyze tab1;
→ 表ロック:4.SHARE UPDATE EXCLUSIVE

vacuum tab1;
→ 表ロック:4.SHARE UPDATE EXCLUSIVE

 

(2014)

https://docs.microsoft.com/ja-jp/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014
https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver15

 

-- ロックリソースタイプ
DATABASE
FILE
OBJECT
PAGE
KEY
EXTENT
RID
APPLICATION
METADATA
HOBT
ALLOCATION_UNIT

 

-- ロック種類

Sch-S (Schema stability)
Sch-M (Schema modification)
S (Shared)
U (Update)
X (Exclusive)
IS (Intent Shared)
IU (Intent Update)
IX (Intent Exclusive)
SIU (Shared Intent Update)
SIX (Shared Intent Exclusive)
UIX (Update Intent Exclusive)
BU
RangeS_S (Shared Key-Range and Shared Resource lock)
RangeS_U (Shared Key-Range and Update Resource lock)
RangeI_N (Insert Key-Range and Null Resource lock)
RangeI_S
RangeI_U
RangeI_X
RangeX_S
RangeX_U
RangeX_X

-- ロック確認

1.テーブル準備
drop table tab1;
create table tab1(col1 int not null,col2 int);
alter table tab1 add constraint tab1pk primary key (col1);
insert into tab1 values(1,1);
select * from tab1;

2.セッションID確認

select @@spid;

3.確認SQL

SELECT * FROM sys.dm_tran_locks;
go

SELECT *
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id
=(SELECT TOP 1 object_id FROM sys.partitions WHERE OBJECT_NAME(object_id)='tab1');
go


-- ロック確認結果
※テーブルロックのみ記載

select * from tab1 where col1 = 1;
→ Sch-S (Schema stability)

select * from tab1 WITH (NOLOCK) where col1 = 1;
→ Sch-S (Schema stability)

select * from tab1 WITH (UPDLOCK,ROWLOCK) where col1 = 1;
→ IX (Intent Exclusive)

select * from tab1 WITH (XLOCK,ROWLOCK) where col1 = 1;
→ IX (Intent Exclusive)


insert into tab1 values(2,2);
→ IX (Intent Exclusive)

update tab1 set col2 = 2 where col1 = 1;
→ IX (Intent Exclusive)

delete from tab1 where col1 = 1;
→ IX (Intent Exclusive)


create index ind12 on tab1(col2) with (online = on);
→ S (Shared)

drop index ind12 on tab1;
→ Sch-M (Schema modification)


alter table tab1 add col3 int;
→ Sch-M (Schema modification)

alter table tab1 drop column col3;
→ Sch-M (Schema modification)

update statistics tab1;
→ Sch-S (Schema stability)