[20171120] 11g select for update skip locked.txt, 20171120locked.txt

[20171120] 11g select for update skip locked.txt, 20171120locked.txt

[20171120] 11g select for update skip locked.txt

-- // 11g when the select for update encounters a blocking, you can skip the blocking record through skipped locked. Let's test the following:

1. Environment:
SCOTT @ book> @ & r/ver1
PORT_STRING VERSION BANNER
----------------------------------------------------------------------------------------------------------------------------
X86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

SCOTT @ book> create table empx as select * from emp;
Table created.

2. test:
-- // Session 1:
SCOTT @ book> select count (*) from empx;
COUNT (*)
------------
14

SCOTT @ book> update empx set sal = sal + 100 where deptno = 10;
3 rows updated.

-- // Sesson 2:
SCOTT @ book> select * from empx for update;

-- // Suspend !!

-- // Session 1:
SCOTT @ book> @ & r/viewlock
Sid serial # username osuser machine module LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
80 15 SCOTT oracle xxxxxxx SQL * Plus TM DML (TM) Row-X (SX) None 90707 0 SCOTT TABLE EMPX No partition limit 84c350b8
80 15 SCOTT oracle xxxxxxx SQL * Plus TX Transaction Exclusive None 589847 2894 No supported limit 84c350b8
80 15 SCOTT oracle xxxxxxx SQL * Plus TX Transaction None Exclusive 655366 22646 No supported limit 84c350b8
274 9 SCOTT oracle xxxxxxx SQL * Plus TX Transaction Exclusive None 655366 22646 Yes
274 9 SCOTT oracle xxxxxxx SQL * Plus TM DML (TM) Row-X (SX) None 90707 0 SCOTT TABLE EMPX No

SCOTT @ book> select * from empx for update skip locked;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------------------------------
7369 smith clerk 7902 00:00:00 800 20
7499 allen salesman 7698 00:00:00 1600 300 30
7521 ward salesman 7698 00:00:00 1250 500 30
7566 jones manager 7839 00:00:00 2975 20
7654 martin salesman 7698 00:00:00 1250 1400 30
7698 blake manager 7839 00:00:00 2850 30
7788 scott analyst 7566 00:00:00 3000 20
7844 turner salesman 7698 00:00:00 1500 0 30
7876 adams clerk 7788 00:00:00 1100 20
7900 james clerk 7698 00:00:00 950 30
7902 ford analyst 7566 00:00:00 3000 20
11 rows selected.

-- // 11 records with no lock are displayed.