论坛 › Oracle数据库管理 › 怎么理解oracle的锁类型和工作机制呢？

geekwolf 发表于 2011-10-27 10:48:34

怎么理解oracle的锁类型和工作机制呢？

怎么理解oracle的锁类型和工作机制呢？

Liu Maclean(刘相兵 发表于 2011-10-27 15:43:47

网友你好，


这个问题 关于锁和oracle的工作机制，所要论及的知识点过于宽泛了，这不是提问的方式。
如Oracle中 光锁就有好几种：enqueue lock 和 ddl lock 等。

搞清楚你真正想要问的细节，这样可以给予你参考

ALLSTARS_ORACLE 发表于 2017-4-17 17:03:26

ding ding ding

biotwang 发表于 2017-8-16 16:14:16

关于锁

有时候一些和性能相关的问题可以源于锁。特别当你在AWR或SQL Trace/TKPROF中看到以下等待事件频繁发生的时候：
  ● enq: TX - row lock contention
  ● enq: TX - allocate ITL entry
  ● enq: TM – contention
  ● enq: UL - contention

请注意，等待事件“PL/SQL lock timer”并不是由会话见的锁引起的，而是由应用程序代码中的USER_LOCK.SLEEP或DBMS_LOCK.SLEEP procedure造成的。
在文档《Oracle Database Concepts Guide》第9章（Data Concurrency and Consistency, Overview of the Oracle Database Locking Mechanism）中有对于Oracle数据库锁机制的最佳描述。其中也覆盖了latch，mutex和internal lock的知识。
MOS支持文件"FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors (Document 15476.1)"也对Oracle如何处理锁进行了较好的解释。
Oracle有3种主要类型的锁(可能遇到的)，TX，TM和UL。以下为这三种锁的简要描述。

16.1 TX (行锁)
当事务启动时，一个TX队列被建立起来。它会以一个唯一的回滚段号、slot号（在回滚段事务表中）和slot号下的子序列号来进行指定。
对于这种锁，等待事件一般是"enq: TX - row lock contention"。
当一个会话正对某行执行DML命令时（或 SELECT ... FOR UPDATE），可以由于以下原因等待TX锁：
  ● 存在另一个会话正在对此需求行执行DML或已经使用了SELECT ... FOR UPDATE。
  ● 存在另一个会话刚插入相同的行（存在唯一键索引）进入相同的表。
  ● 在块头上当前没有空余的ITL(interested transaction list) slot可分配。例如：在同一个块上有太多并行DML事务正在对行进行操作。可以考虑对段增加INITRANS（和/或MAXTRANS）。也可以通过重新进行表组织来降低块中的行数；或降低块大小；增加PCTFREE。相应情况对应的等待事件为"enq: TX - allocate ITL entry"。

MOS支持文件"Waits for 'Enq: TX - ...' Type Events - Transaction (TX) Lock Example Scenarios (Document 62354.1)"描述了TX锁的一些场景，包括有：
  ● 块中ITL slot不足（INITRANS）
  ● 由于行正被同一个BITMAP索引片所涉及而导致等待

16.2 TM (表锁)
这类锁可以被一个事务通过DML语句（或SELECT ... FOR UPDATE）获取或通过LOCK TABLE语句获取。
获取表锁的DML操作是为了对表之后的DML事务访进行保护，以避免其它会话可能的DDL操作和事务起冲突。
此类锁的等待事件为"enq: TM – contention"。

16.3 UL (用户锁)
数据库中也存在用户定义的锁。
它们的上锁和释放使用的是DBMS_LOCK包下的procedure REQUEST和RELEASE。
对应的等待事件为"enq: UL - contention"

biotwang 发表于 2017-8-16 16:15:08

16.4 确认哪里存在锁等待
以下查询确认了持有的锁和其最长持有时间，以及相应受到阻塞的会话：SELECT
   lr.sid blocker_sid,
   ld.sid blocked_sid,
   lr.type, -- Type will typically be TM, TX, UL. Otherwise it is system type.
   lt.name,
   lr.id1, -- The value contained in these varies. For TM ID1 contains the object_id, for TX it is the Rollback entry
   lr.id2, -- The value contained in these varies. For TX it is the Transaction entry.
   decode(lr.lmode,
          0,'none(0)',
          1,'null(NULL)(1)',
          2,'row-S(SS)(2)',
          3,'row-S(SX)(3)',
          4,'Share(S)(4)',
          5,'S/Row-X(SSX)(5)',
          6,'exclusive(X)(6)',
          LTRIM(TO_CHAR(lr.lmode,'990'))) lock_hold_mode,
   decode(lr.request,
          0,'none(0)',
          1,'null(NULL)(1)',
          2,'row-S(SS)(2)',
          3,'row-S(SX)(3)',
          4,'Share(S)(4)',
          5,'S/Row-X(SSX)(5)',
          6,'exclusive(X)(6)',
          LTRIM(TO_CHAR(lr.request,'990'))) lock_request_mode,
   lr.ctime time_blocker_held,
   ld.ctime time_blocked_waiting
FROM v$lock lr,
     v$lock ld,
     v$lock_type lt
WHERE lt.type (+) = lr.type
AND ld.id1 = lr.id1 -- rollback entries match
AND ld.id2 = lr.id2 -- transaction entries match
AND lr.block = 1 -- blocker
AND ld.block = 0 -- blocked
ORDER BY lr.ctime DESC;  下面这个查询则提供了锁链情况 - 以链的长度排序，最长的在最前面：
主要的受阻会话可以通过这个查询来确认。
注意：如果存在持有锁的会话阻塞了多个其它会话的情况，那么部分锁链在报告中会被报告多次的情况（多行相同，每行对应一个链组合的起始和结束）。SELECT *
FROM
   (SELECT
       level chainlevel,
       blocked_sid,
       CONNECT_BY_ROOT blocker_sid ultimate_blocker_sid,
       sys_connect_by_path(blocker_sid,'/')|| '/' || blocked_sid blocking_path,
       time_blocked_waiting
    FROM (SELECT
             lr.sid blocker_sid,
             ld.sid blocked_sid,
             lr.id1,
             lr.id2,
             ld.ctime time_blocked_waiting
          FROM v$lock lr,
               v$lock ld
          WHERE ld.id1 = lr.id1
          AND ld.id2 = lr.id2
          AND lr.block = 1
          AND ld.block = 0) linked_locks
    CONNECT BY NOCYCLE PRIOR blocked_sid = blocker_sid) chains
-- blocked end of chain is not a blocker session
WHERE NOT EXISTS (SELECT 'exists'
                  FROM v$lock lr,
                       v$lock ld
                  WHERE ld.id1 = lr.id1
                  AND ld.id2 = lr.id2
                  AND lr.block = 1
                  AND ld.block = 0
                  AND lr.sid = chains.blocked_sid)
-- blocker end of chain is not a blocked session
AND NOT EXISTS (SELECT 'exists'
                FROM v$lock lr,
                     v$lock ld
                WHERE ld.id1 = lr.id1
                AND ld.id2 = lr.id2
                AND lr.block = 1
                AND ld.block = 0
                AND ld.sid = chains.ultimate_blocker_sid)
ORDER BY chainlevel DESC; 下面的查询则显示了死循环/死锁问题：SELECT *
FROM (SELECT
         DECODE(CONNECT_BY_ISCYCLE,1,'YES','NO') is_a_cycle,
         level,
         blocked_sid,
         CONNECT_BY_ROOT blocker_sid ultimate_blocker_sid,
         sys_connect_by_path(blocker_sid,'/')|| '/' || blocked_sid blocking_path,
         time_blocked_waiting
      FROM (SELECT
               lr.sid blocker_sid,
               ld.sid blocked_sid,
               lr.id1,
               lr.id2,
               ld.ctime time_blocked_waiting
            FROM v$lock lr,
                 v$lock ld
            WHERE ld.id1 = lr.id1
            AND ld.id2 = lr.id2
            AND lr.block = 1
            AND ld.block = 0) linked_locks
      CONNECT BY NOCYCLE PRIOR blocked_sid = blocker_sid)
WHERE is_a_cycle = 'YES';更多会话信息，按锁时间长短排序：SELECT
   l.sid,
   l.block,
   DECODE(l.block,1,'Blocker','Blocked'),
   s.blocking_session,
   l.type,
   lt.name,
   l.id1,
   l.id2,
   decode(l.lmode,
          0,'none(0)',
          1,'null(NULL)(1)',
          2,'row-S(SS)(2)',
          3,'row-S(SX)(3)',
          4,'Share(S)(4)',
          5,'S/Row-X(SSX)(5)',
          6,'exclusive(X)(6)',
          LTRIM(TO_CHAR(l.lmode,'990')) ) lock_hold_mode,
   decode(l.request,
          0,'none(0)',
          1,'null(NULL)(1)',
          2,'row-S(SS)(2)',
          3,'row-S(SX)(3)',
          4,'Share(S)(4)',
          5,'S/Row-X(SSX)(5)',
          6,'exclusive(X)(6)',
          LTRIM(TO_CHAR(l.request, '990')) ) lock_request_mode,
   l.ctime time_held_s,
   s.seconds_in_wait,
   s.wait_class,
   s.event,
   s.p1text,
   s.p1,
   s.p1raw,
   s.p2text,
   s.p2,
   s.p2raw,
   s.p3text,
   s.p3,
   s.p3raw,
   s.serial#,
   s.program,
   s.client_identifier,
   s.terminal,
   s.command,
   ct.command_name,
   s.service_name,
   s.module,
   s.action,
   s.username,
   s.machine,
   DECODE(l.type, 'TM', o.object_name) object_name,
   s.sql_id,
   st.sql_text, -- just the first 64 characters of the SQL text
   st.hash_value,
   s.row_wait_obj#,
   s.row_wait_file#,
   s.row_wait_block#,
   s.row_wait_row#
FROM v$session s,
     v$lock l,
     v$sqltext st,
     v$lock_type lt,
     v$sqlcommand ct,
     dba_objects o
WHERE s.sid (+) = l.sid
AND (   (l.request != 0) -- either requesting a lock
      OR
        (l.lmode != 0 -- or holding a lock
         AND
         EXISTS (SELECT 'exists'
                 FROM v$lock ld
                 WHERE ld.request != 0
                 AND ld.id1 = l.id1
                 AND ld.id2 = l.id2) -- and blocking a blocked session
        )
    )
AND st.sql_id (+) = s.sql_id
AND st.piece (+) = 0
AND o.object_id (+) = l.id1
AND lt.type (+) = l.type
AND ct.command_type (+) = s.command
-- AND l.sid = <session id>
ORDER BY l.ctime DESC;
针对某个被堵塞的会话所涉及对象及rowid，我们可以使用以下SQL查看：
这主要是针对行锁(TX)，但也会显示TM锁的一些相关信息，这取决于表上的锁的模式以及被锁会话正在进行的活动。SELECT
   o.object_name,
   s.row_wait_obj#,
   s.row_wait_file#,
   s.row_wait_block#,
   s.row_wait_row#,
   dbms_rowid.rowid_create( 0, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#) restricted_rowid,
   dbms_rowid.rowid_create( 1, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#) extended_rowid
FROM v$session s,
     dba_objects o
WHERE s.row_wait_obj# = o.object_id (+)
AND s.sid = <sid>;rowid可以被用于指出表上被锁住的行。
以下SQL可用于通过sql id来得到SQL具体信息：
The rowid could be used to query the contents of the locked row on the object (table).
The following SQL can be run for particular sql ids to get more SQL text:SELECT
   sql_text -- First 1000 characters
FROM v$sql
WHERE sql_id = <sql_id>;
or:
SELECT
   sql_text
FROM v$sqltext
WHERE sql_id = <sql_id>
ORDER BY piece;

biotwang 发表于 2017-8-16 16:18:18

16.5 定位被锁住的位置
视图v$lock，v$session上的信息仅是针对当前的会话。
不过，Active Session History (ASH)可能有收集到更长时间前（几十秒前）的所数据。
以下查询是从Active Session History (在DBA_ACTIVE_SESS_HISTORY)中记录的行、表、用户锁的记录来获取的信息。类似也可通过视图V$ACTIVE_SESSION_HISTORY来查看最近的历史信息（如果还未被清理至DBA_ACTIVE_SESS_HISTORY表中的话）SELECT
   s.session_id,
   s.blocking_session,
   s.session_serial#,
   s.sql_id,
   s.wait_class,
   s.event,
   s.p1text,
   s.p1,
   s.p2text,
   s.p2,
   s.p3text,
   s.p3,
   o.object_type,
   o.object_name,
   s.current_obj#,
   s.current_file#,
   s.current_block#,
   s.current_row#,
   s.program,
   s.module,
   s.action,
   s.client_id,
   s.machine,
   COUNT(*)*10 approx_wait_secs, -- note that if 10 seconds is reported then it could be a lot less
   MIN(s.sample_time) start_sample_time,
   MAX(s.sample_time) end_sample_time
FROM dba_active_sess_history s,
     dba_hist_seg_stat_obj o
WHERE s.dbid = <db_id>
AND s.instance_number = <inst_num>
AND s.snap_id BETWEEN <begin_snap> and <end_snap>
AND o.dbid (+) = s.dbid
AND o.obj# (+) = s.current_obj#
AND s.blocking_session IS NOT NULL
AND s.event IN
    ('enq: TX - row lock contention'
    ,'enq: TM - contention'
    ,'enq: UL - contention'
    ,'enq: TX - allocate ITL entry')
GROUP BY s.session_id, s.blocking_session, s.session_serial#, s.sql_id, s.wait_class, s.event, s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3, o.object_type, o.object_name, s.current_obj#, s.current_file#, s.current_block#, s.current_row#, s.program, s.module, s.action, s.client_id, s.machine
ORDER BY COUNT(*) DESC;以下查询则提供了锁链的相关信息（由于表、行或用户锁），这些已经持续了一段时间的锁链情况，按链的长度排序：SELECT
   level,
   sample_time,
   session_id blocked_sid,
   CONNECT_BY_ROOT blocking_session ultimate_blocker_sid,
   sys_connect_by_path(blocking_session,'/')|| '/' || session_id blocking_path
FROM
   (-- Blocked Sessions
    SELECT
       s.session_id,
       s.blocking_session,
       s.sample_time
    FROM dba_active_sess_history s
    WHERE s.dbid = <db_id>
    AND s.instance_number = <inst_num>
    AND s.snap_id BETWEEN <begin_snap> and <end_snap>
    AND s.blocking_session IS NOT NULL
    AND s.event IN
        ('enq: TX - row lock contention'
        ,'enq: TM - contention'
        ,'enq: UL - contention'
        ,'enq: TX - allocate ITL entry')
    UNION
    -- Blocking Sessions
    SELECT s.session_id,
           s.blocking_session,
           s.sample_time
    FROM dba_active_sess_history s
    WHERE s.dbid = <db_id>
    AND s.instance_number <inst_num>
    AND s.snap_id BETWEEN <begin_snap> and <end_snap>
    AND s.blocking_session IS NULL
    AND s.event IN
        ('enq: TX - row lock contention'
        ,'enq: TM - contention'
        ,'enq: UL - contention'
        ,'enq: TX - allocate ITL entry')
    AND EXISTS
       (SELECT 'exists'
        FROM DBA_HIST_ACTIVE_SESS_HISTORY bs
        WHERE bs.dbid = <db_id>
        AND bs.instance_number = <inst_num>
        AND bs.snap_id BETWEEN <begin_snap> and <end_snap>
        AND bs.blocking_session = s.session_id
        AND bs.sample_time = s.sample_time
        AND bs.blocking_session IS NOT NULL
        AND bs.event IN
            ('enq: TX - row lock contention'
            ,'enq: TM - contention'
            ,'enq: UL - contention'
            ,'enq: TX - allocate ITL entry')
       )
   )
CONNECT BY NOCYCLE PRIOR session_id = blocking_session
                   AND PRIOR sample_time = sample_time
ORDER BY level DESC, blocked_sid, sample_time;

biotwang 发表于 2017-8-16 16:19:16

16.6 "PL/SQL lock timer"等待事件
此类事件中，会话之间没有锁和交互。
通过调用USER_LOCK.SLEEP 或 DBMS_LOCK.SLEEP，会话在预先指定的时间长度中进行休眠。
不过它表现得就像会话被锁住了或存在性能问题一样。
"PL/SQL lock timer"等待的会话信息可以通过以下查询找到：SELECT
   s.sid,
   s.seconds_in_wait,
   s.wait_class,
   s.event,
   s.p1text,
   s.p1,
   s.p1raw,
   s.serial#,
   s.program,
   s.client_identifier,
   s.terminal,
   s.command,
   ct.command_name,
   s.service_name,
   s.module,
   s.action,
   s.username,
   s.machine,
   s.sql_id,
   st.sql_text, -- just the first 64 characters of the SQL text
   st.hash_value
FROM v$session s,
     v$sqltext st,
     v$sqlcommand ct
WHERE st.sql_id (+) = s.sql_id
AND st.piece (+) = 0
AND ct.command_type (+) = s.command
AND s.event = 'PL/SQL lock timer'
ORDER BY s.seconds_in_wait DESC;16.7 从SQL Trace或Active Session History (ASH)中找到TX锁模式信息
如果一个TX锁出现在了trace日志或Active Session History (ASH)中，那么可以使用以下方式判断TX锁模式：
转换模式（e.g. 1415053318）到十六进制hex值（e.g. 54580006）。
这个可以通过以下方式来完成：SELECT TRIM(TO_CHAR(1415053318,'XXXXXXXXX')) FROM dual; 注意，列V$SESSION.p1raw在任何情况下，存放的都是hex值。
前4个数字告知了锁类型，在ASCII中 Hex 54 = T， 58 = X。
最后四个数字则告知了锁模式（这也可以从v$lock中的持有会话中找到）。
See My Oracle Support document "How to Determine The Lock Type and Mode from an Enqueue Wait (Document 413934.1)".

查看完整版本: 怎么理解oracle的锁类型和工作机制呢？