Segment level locking in Oracle

Posted on | Edited on | In

Simple demonstration of segment-level locking caused by concurrency in an Oracle database

reprint:简单演示 Oracle 数据库并发导致段级锁（表级锁）
https://www.cnblogs.com/liuning8023/archive/2013/03/20/2971946.html

content

  • environment
  • demonstration

本文简单演示并发导致的行级锁。并发是两个以上的用户对同样的数据进行修改（包括插入、删除和修改）。锁的产生是因为并发。没有并发，就没有锁。并发的产生是因为系统需要，系统需要是因为用户需要。

This article briefly demonstrates row-level locking caused by concurrency. Concurrency is when two or more users make changes (including inserts, deletes, and modifications) to the same data. Locks occur because of concurrency. No concurrency, no locking. Concurrency occurs because the system needs it, and the system needs it because the user needs it.

environment

  • Windows 2003 Server
  • Oracle 11g Release 1 (11.1)

demonstration

open the first sql session

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> select distinct sid from v$mystat;

       SID
----------
       124

SQL> create table t(x int) partition by range(x)(partition p1 values less than(10),partition p2 values less than(maxvalue));

表已创建。

SQL> insert into t values(1);

已创建 1 行。

SQL> select * from t partition(p1);

         X
----------
         1

SQL> select sid,type,id1,id2,lmode,request,block
  2  from v$lock where sid=124;

       SID TYPE         ID1        ID2      LMODE    REQUEST      BLOCK
---------- ----- ---------- ---------- ---------- ---------- ----------
       124 AE            99          0          4          0          0
       124 TM        128807          0          3          0          0
       124 TM        128808          0          3          0          0
       124 TX        589847      46045          6          0          0

SQL> select object_name, subobject_name
  2    from dba_objects
  3   where object_id in (128807, 128808);

OBJECT_NAME     SUBOBJECT_NAME
--------------- ---------------
T
T               P1

SQL>
  • create partition table t, with two partitions, p1 and p2
  • insert a record into p1
  • find TM share lock in t and p1, by view V$LOCK
  • ID1 represents the locked object ID. You can get the object name by looking at the DBA_OBJECTS view. 128807 and 128808 correspond to the p1 partition of table t and table t, respectively.

open the second sql session

At this point, what happens if we do DDL operations on t table, p1 partition of t table, and p2 partition of t table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select distinct sid from v$mystat;

       SID
----------
       140

SQL> truncate table t;
truncate table t
               *
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效


SQL> alter table t truncate partition p1;
alter table t truncate partition p1
            *
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效


SQL> alter table t truncate partition p2;

表被截断。

SQL>

  • Only the p2 partition is not section-locked, so DDL operations can be performed on p2.

  • A TM lock is a segmental-level lock that allows locks at the same level or lower, but rejects higher-level locks, making the DDL operation significantly higher. Oracle minimizes the scope of the lock as much as possible.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> insert into t values(11);

已创建 1 行。

SQL> select * from t partition(p2);

         X
----------
        11

SQL> select sid,type,id1,id2,lmode,request,block
  2  from v$lock where sid in (124,140)
  3  order by sid,type;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       124 AE         99          0          4          0          0
       124 TM     128808          0          3          0          0
       124 TM     128807          0          3          0          0
       124 TX     327711      45817          6          0          0
       140 AE         99          0          4          0          0
       140 TM     128807          0          3          0          0
       140 TM     128809          0          3          0          0
       140 TX     196611      45960          6          0          0

已选择8行。

SQL> select object_name, subobject_name
  2    from dba_objects
  3   where object_id in (128807, 128808, 128809);

OBJECT_NAME     SUBOBJECT_NAME
--------------- ---------------
T
T               P1
T               P2

SQL>

  • When a piece of data is inserted into a p2 partition, a Shared lock is also added to the p2 partition, i.e. ID1=128809.

  • A TM lock is a table-level Shared lock. A table is usually viewed as a segment. When a table has several segments, each segment is individually locked.

summary

  • segmental-level lock allows locks at the same level or lower, but rejects higher-level locks. so p1 reject DDL, but p2 truncated.