(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)