ロック行のスキップ

• MySQL

(8.0.21)

drop table tab1;
create table tab1(col1 int,col2 int,col3 varchar(10) );

create index ind11 on tab1(col1);

insert into tab1 values(1,100,'off');
insert into tab1 values(2,100,'off');
insert into tab1 values(3,100,'off');
select * from tab1;

-- session1
start transaction;
select * from tab1 where col2 = 100 and col3 = 'off' order by col1 limit 1 for update;

-- session2
start transaction;
select * from tab1 where col2 = 100 and col3 = 'off' order by col1 limit 1 for update;

→待ちになる

start transaction;
select * from tab1 where col2 = 100 and col3 = 'off' order by col1 limit 1 for update skip locked;

→col1 = 2 の行でロック取得できる

• Oracle

(12cR1)

drop table tab1 purge;
create table tab1(col1 int,col2 int,col3 varchar2(10) );

create index ind11 on tab1(col1);

insert into tab1 values(1,100,'off');
insert into tab1 values(2,100,'off');
insert into tab1 values(3,100,'off');
commit;

select * from tab1;

-- session1

select * from tab1 where col2 = 100 and col3 = 'off' order by col1 fetch first 1 rows only for update;

→ORA-02014: DISTINCT、GROUP BYなどを含むビューに対してFOR UPDATE句を使用できません

select * from tab1 where col2 = 100 and col3 = 'off' and rownum <= 1 for update;

-- session2

select * from tab1 where col2 = 100 and col3 = 'off' and rownum <= 1 for update;

→待ちになる

select * from tab1 where col2 = 100 and col3 = 'off' and rownum <= 1 for update skip locked;

→レコードが選択されませんでした。

declare
cursor cur1 is select * from tab1 where col2 = 100 and col3 = 'off' order by col1;
rtab1 tab1%rowtype;
begin
for c1 in cur1 loop
begin
select * into rtab1 from tab1 where col1 = c1.col1 for update;
update tab1 set col3 = 'on' where col1 = c1.col1 ;
commit;
exit;
exception
when no_data_found then
null;
end;
end loop;
end;
/

→待ちになる

declare
cursor cur1 is select * from tab1 where col2 = 100 and col3 = 'off' order by col1;
rtab1 tab1%rowtype;
begin
for c1 in cur1 loop
begin
select * into rtab1 from tab1 where col1 = c1.col1 for update skip locked;
update tab1 set col3 = 'on' where col1 = c1.col1 ;
commit;
exit;
exception
when no_data_found then
null;
end;
end loop;
end;
/

→col1 = 2 の行でロック取得できる

• PostgreSQL

(11)

drop table tab1;
create table tab1(col1 int,col2 int,col3 varchar(10) );

create index ind11 on tab1(col1);

insert into tab1 values(1,100,'off');
insert into tab1 values(2,100,'off');
insert into tab1 values(3,100,'off');
select * from tab1;

-- session1
start transaction;
select * from tab1 where col2 = 100 and col3 = 'off' order by col1 limit 1 for update;

-- session2
start transaction;
select * from tab1 where col2 = 100 and col3 = 'off' order by col1 limit 1 for update;

→待ちになる

start transaction;
select * from tab1 where col2 = 100 and col3 = 'off' order by col1 limit 1 for update skip locked;

→col1 = 2 の行でロック取得できる

• SQL Server

(2019)

drop table tab1;
create table tab1(col1 int,col2 int,col3 varchar(10) );

create index ind11 on tab1(col1);

insert into tab1 values(1,100,'off');
insert into tab1 values(2,100,'off');
insert into tab1 values(3,100,'off');
select * from tab1;

-- session1
begin transaction;
select top 1 * from tab1 WITH (UPDLOCK,ROWLOCK,READPAST) where col2 = 100 and col3 = 'off' order by col1;

-- session2
begin transaction;
select top 1 * from tab1 WITH (UPDLOCK,ROWLOCK) where col2 = 100 and col3 = 'off' order by col1;

→待ちになる

begin transaction;
select top 1 * from tab1 WITH (UPDLOCK,ROWLOCK,READPAST) where col2 = 100 and col3 = 'off' order by col1;

→col1 = 2 の行でロック取得できる