skip locked

来源：互联网发布：淘宝开店装修流程编辑：程序博客网时间：2024/06/01 07:12

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查询结果集的基础上进行二次筛选。