Row Exclusive Table Lock (RX)
This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.
eg：
session 1：
SCOTT@orcl#select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
124 0 1
SCOTT@orcl#select * from emp for update
2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 4000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS hello 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7000 MILLER hello 2000 23-1月 -82 2000 10
已选择12行。
session 2：
SCOTT@orcl#
SQL> select sid,type,id1,lmode from v$lock where sid=124;
SID TYPE ID1 LMODE
---------- ---- ---------- ----------
124 AE 99 4
124 TM 69539 3
124 TX 458768 6
SQL> select session_id,lock_type,mode_held,lock_id1 from dba_locks where session_id=124;
SESSION_ID LOCK_TYPE MODE_HELD LOCK_ID1
---------- -------------------------- ---------------------------------------- ----------------------------------------
124 AE Share 99
124 DML Row-X (SX) 69539
124 Transaction Exclusive 458768
session3：
然后在在另一个session
update emp set job='hello' where empno=7844;
已更新 1 行。
SCOTT@orcl#update emp set job='hello' where empno=7844;
已更新 1 行。