create table EMPLOYEE
(
EMPID INTEGER,
DEPTID INTEGER,
SALARY NUMBER(10,2)
);
insert into employee (EMPID, DEPTID, SALARY)
values (1, 10, 5500.00);
insert into employee (EMPID, DEPTID, SALARY)
values (2, 10, 4500.00);
insert into employee (EMPID, DEPTID, SALARY)
values (3, 20, 1900.00);
insert into employee (EMPID, DEPTID, SALARY)
values (4, 20, 4800.00);
insert into employee (EMPID, DEPTID, SALARY)
values (5, 40, 6500.00);
commit;
session1:
SQL> select * from employee where empid= 1 for update skip locked;
EMPID DEPTID SALARY
----- -------- ------------
1 10 5500.00
session2:
SQL> select * from employee where empid= 1 for update skip locked;
EMPID DEPTID SALARY
----- -------- ------------
可以看到在session1有数据，原因是同一个SESSION的锁定可以重复获取；session2没有数据，原因是For Update Skip Locked发现记录被锁定, 则被筛选掉，且是在SQL查询结果集的基础上进行二次筛选。