Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 怎么理解oracle的锁类型和工作机制呢？
发新帖
geekwolf

16

积分

0

好友

0

主题

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

1#
发表于 2011-10-27 10:48:34 | 查看: 10865| 回复: 6
怎么理解oracle的锁类型和工作机制呢？
2#
发表于 2011-10-27 15:43:47
网友你好，


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

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

回复 只看该作者 道具 举报

3#
发表于 2017-4-17 17:03:26

ding ding ding

回复 只看该作者 道具 举报

4#
发表于 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"

回复 只看该作者 道具 举报

5#
发表于 2017-8-16 16:15:08
16.4 确认哪里存在锁等待
以下查询确认了持有的锁和其最长持有时间，以及相应受到阻塞的会话：
  1. SELECT
  2.    lr.sid blocker_sid,
  3.    ld.sid blocked_sid,
  4.    lr.type, -- Type will typically be TM, TX, UL. Otherwise it is system type.
  5.    lt.name,
  6.    lr.id1, -- The value contained in these varies. For TM ID1 contains the object_id, for TX it is the Rollback entry
  7.    lr.id2, -- The value contained in these varies. For TX it is the Transaction entry.
  8.    decode(lr.lmode,
  9.           0,'none(0)',
  10.           1,'null(NULL)(1)',
  11.           2,'row-S(SS)(2)',
  12.           3,'row-S(SX)(3)',
  13.           4,'Share(S)(4)',
  14.           5,'S/Row-X(SSX)(5)',
  15.           6,'exclusive(X)(6)',
  16.           LTRIM(TO_CHAR(lr.lmode,'990'))) lock_hold_mode,
  17.    decode(lr.request,
  18.           0,'none(0)',
  19.           1,'null(NULL)(1)',
  20.           2,'row-S(SS)(2)',
  21.           3,'row-S(SX)(3)',
  22.           4,'Share(S)(4)',
  23.           5,'S/Row-X(SSX)(5)',
  24.           6,'exclusive(X)(6)',
  25.           LTRIM(TO_CHAR(lr.request,'990'))) lock_request_mode,
  26.    lr.ctime time_blocker_held,
  27.    ld.ctime time_blocked_waiting
  28. FROM v$lock lr,
  29.      v$lock ld,
  30.      v$lock_type lt
  31. WHERE lt.type (+) = lr.type
  32. AND ld.id1 = lr.id1 -- rollback entries match
  33. AND ld.id2 = lr.id2 -- transaction entries match
  34. AND lr.block = 1 -- blocker
  35. AND ld.block = 0 -- blocked
  36. ORDER BY lr.ctime DESC;  
复制代码
下面这个查询则提供了锁链情况 - 以链的长度排序，最长的在最前面：
主要的受阻会话可以通过这个查询来确认。
注意：如果存在持有锁的会话阻塞了多个其它会话的情况，那么部分锁链在报告中会被报告多次的情况（多行相同，每行对应一个链组合的起始和结束）。
  1. SELECT *
  2. FROM
  3.    (SELECT
  4.        level chainlevel,
  5.        blocked_sid,
  6.        CONNECT_BY_ROOT blocker_sid ultimate_blocker_sid,
  7.        sys_connect_by_path(blocker_sid,'/')|| '/' || blocked_sid blocking_path,
  8.        time_blocked_waiting
  9.     FROM (SELECT
  10.              lr.sid blocker_sid,
  11.              ld.sid blocked_sid,
  12.              lr.id1,
  13.              lr.id2,
  14.              ld.ctime time_blocked_waiting
  15.           FROM v$lock lr,
  16.                v$lock ld
  17.           WHERE ld.id1 = lr.id1
  18.           AND ld.id2 = lr.id2
  19.           AND lr.block = 1
  20.           AND ld.block = 0) linked_locks
  21.     CONNECT BY NOCYCLE PRIOR blocked_sid = blocker_sid) chains
  22. -- blocked end of chain is not a blocker session
  23. WHERE NOT EXISTS (SELECT 'exists'
  24.                   FROM v$lock lr,
  25.                        v$lock ld
  26.                   WHERE ld.id1 = lr.id1
  27.                   AND ld.id2 = lr.id2
  28.                   AND lr.block = 1
  29.                   AND ld.block = 0
  30.                   AND lr.sid = chains.blocked_sid)
  31. -- blocker end of chain is not a blocked session
  32. AND NOT EXISTS (SELECT 'exists'
  33.                 FROM v$lock lr,
  34.                      v$lock ld
  35.                 WHERE ld.id1 = lr.id1
  36.                 AND ld.id2 = lr.id2
  37.                 AND lr.block = 1
  38.                 AND ld.block = 0
  39.                 AND ld.sid = chains.ultimate_blocker_sid)
  40. ORDER BY chainlevel DESC;
复制代码
下面的查询则显示了死循环/死锁问题：
  1. SELECT *
  2. FROM (SELECT
  3.          DECODE(CONNECT_BY_ISCYCLE,1,'YES','NO') is_a_cycle,
  4.          level,
  5.          blocked_sid,
  6.          CONNECT_BY_ROOT blocker_sid ultimate_blocker_sid,
  7.          sys_connect_by_path(blocker_sid,'/')|| '/' || blocked_sid blocking_path,
  8.          time_blocked_waiting
  9.       FROM (SELECT
  10.                lr.sid blocker_sid,
  11.                ld.sid blocked_sid,
  12.                lr.id1,
  13.                lr.id2,
  14.                ld.ctime time_blocked_waiting
  15.             FROM v$lock lr,
  16.                  v$lock ld
  17.             WHERE ld.id1 = lr.id1
  18.             AND ld.id2 = lr.id2
  19.             AND lr.block = 1
  20.             AND ld.block = 0) linked_locks
  21.       CONNECT BY NOCYCLE PRIOR blocked_sid = blocker_sid)
  22. WHERE is_a_cycle = 'YES';
复制代码
更多会话信息，按锁时间长短排序：
  1. SELECT
  2.    l.sid,
  3.    l.block,
  4.    DECODE(l.block,1,'Blocker','Blocked'),
  5.    s.blocking_session,
  6.    l.type,
  7.    lt.name,
  8.    l.id1,
  9.    l.id2,
  10.    decode(l.lmode,
  11.           0,'none(0)',
  12.           1,'null(NULL)(1)',
  13.           2,'row-S(SS)(2)',
  14.           3,'row-S(SX)(3)',
  15.           4,'Share(S)(4)',
  16.           5,'S/Row-X(SSX)(5)',
  17.           6,'exclusive(X)(6)',
  18.           LTRIM(TO_CHAR(l.lmode,'990')) ) lock_hold_mode,
  19.    decode(l.request,
  20.           0,'none(0)',
  21.           1,'null(NULL)(1)',
  22.           2,'row-S(SS)(2)',
  23.           3,'row-S(SX)(3)',
  24.           4,'Share(S)(4)',
  25.           5,'S/Row-X(SSX)(5)',
  26.           6,'exclusive(X)(6)',
  27.           LTRIM(TO_CHAR(l.request, '990')) ) lock_request_mode,
  28.    l.ctime time_held_s,
  29.    s.seconds_in_wait,
  30.    s.wait_class,
  31.    s.event,
  32.    s.p1text,
  33.    s.p1,
  34.    s.p1raw,
  35.    s.p2text,
  36.    s.p2,
  37.    s.p2raw,
  38.    s.p3text,
  39.    s.p3,
  40.    s.p3raw,
  41.    s.serial#,
  42.    s.program,
  43.    s.client_identifier,
  44.    s.terminal,
  45.    s.command,
  46.    ct.command_name,
  47.    s.service_name,
  48.    s.module,
  49.    s.action,
  50.    s.username,
  51.    s.machine,
  52.    DECODE(l.type, 'TM', o.object_name) object_name,
  53.    s.sql_id,
  54.    st.sql_text, -- just the first 64 characters of the SQL text
  55.    st.hash_value,
  56.    s.row_wait_obj#,
  57.    s.row_wait_file#,
  58.    s.row_wait_block#,
  59.    s.row_wait_row#
  60. FROM v$session s,
  61.      v$lock l,
  62.      v$sqltext st,
  63.      v$lock_type lt,
  64.      v$sqlcommand ct,
  65.      dba_objects o
  66. WHERE s.sid (+) = l.sid
  67. AND (   (l.request != 0) -- either requesting a lock
  68.       OR
  69.         (l.lmode != 0 -- or holding a lock
  70.          AND
  71.          EXISTS (SELECT 'exists'
  72.                  FROM v$lock ld
  73.                  WHERE ld.request != 0
  74.                  AND ld.id1 = l.id1
  75.                  AND ld.id2 = l.id2) -- and blocking a blocked session
  76.         )
  77.     )
  78. AND st.sql_id (+) = s.sql_id
  79. AND st.piece (+) = 0
  80. AND o.object_id (+) = l.id1
  81. AND lt.type (+) = l.type
  82. AND ct.command_type (+) = s.command
  83. -- AND l.sid = <session id>
  84. ORDER BY l.ctime DESC;
复制代码
针对某个被堵塞的会话所涉及对象及rowid，我们可以使用以下SQL查看：
这主要是针对行锁(TX)，但也会显示TM锁的一些相关信息，这取决于表上的锁的模式以及被锁会话正在进行的活动。
  1. SELECT
  2.    o.object_name,
  3.    s.row_wait_obj#,
  4.    s.row_wait_file#,
  5.    s.row_wait_block#,
  6.    s.row_wait_row#,
  7.    dbms_rowid.rowid_create( 0, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#) restricted_rowid,
  8.    dbms_rowid.rowid_create( 1, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#) extended_rowid
  9. FROM v$session s,
  10.      dba_objects o
  11. WHERE s.row_wait_obj# = o.object_id (+)
  12. 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:
  1. SELECT
  2.    sql_text -- First 1000 characters
  3. FROM v$sql
  4. WHERE sql_id = <sql_id>;
  5. or:
  6. SELECT
  7.    sql_text
  8. FROM v$sqltext
  9. WHERE sql_id = <sql_id>
  10. ORDER BY piece;
复制代码

回复 只看该作者 道具 举报

6#
发表于 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表中的话）
  1. SELECT
  2.    s.session_id,
  3.    s.blocking_session,
  4.    s.session_serial#,
  5.    s.sql_id,
  6.    s.wait_class,
  7.    s.event,
  8.    s.p1text,
  9.    s.p1,
  10.    s.p2text,
  11.    s.p2,
  12.    s.p3text,
  13.    s.p3,
  14.    o.object_type,
  15.    o.object_name,
  16.    s.current_obj#,
  17.    s.current_file#,
  18.    s.current_block#,
  19.    s.current_row#,
  20.    s.program,
  21.    s.module,
  22.    s.action,
  23.    s.client_id,
  24.    s.machine,
  25.    COUNT(*)*10 approx_wait_secs, -- note that if 10 seconds is reported then it could be a lot less
  26.    MIN(s.sample_time) start_sample_time,
  27.    MAX(s.sample_time) end_sample_time
  28. FROM dba_active_sess_history s,
  29.      dba_hist_seg_stat_obj o
  30. WHERE s.dbid = <db_id>
  31. AND s.instance_number = <inst_num>
  32. AND s.snap_id BETWEEN <begin_snap> and <end_snap>
  33. AND o.dbid (+) = s.dbid
  34. AND o.obj# (+) = s.current_obj#
  35. AND s.blocking_session IS NOT NULL
  36. AND s.event IN
  37.     ('enq: TX - row lock contention'
  38.     ,'enq: TM - contention'
  39.     ,'enq: UL - contention'
  40.     ,'enq: TX - allocate ITL entry')
  41. 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
  42. ORDER BY COUNT(*) DESC;
复制代码
以下查询则提供了锁链的相关信息（由于表、行或用户锁），这些已经持续了一段时间的锁链情况，按链的长度排序：
  1. SELECT
  2.    level,
  3.    sample_time,
  4.    session_id blocked_sid,
  5.    CONNECT_BY_ROOT blocking_session ultimate_blocker_sid,
  6.    sys_connect_by_path(blocking_session,'/')|| '/' || session_id blocking_path
  7. FROM
  8.    (-- Blocked Sessions
  9.     SELECT
  10.        s.session_id,
  11.        s.blocking_session,
  12.        s.sample_time
  13.     FROM dba_active_sess_history s
  14.     WHERE s.dbid = <db_id>
  15.     AND s.instance_number = <inst_num>
  16.     AND s.snap_id BETWEEN <begin_snap> and <end_snap>
  17.     AND s.blocking_session IS NOT NULL
  18.     AND s.event IN
  19.         ('enq: TX - row lock contention'
  20.         ,'enq: TM - contention'
  21.         ,'enq: UL - contention'
  22.         ,'enq: TX - allocate ITL entry')
  23.     UNION
  24.     -- Blocking Sessions
  25.     SELECT s.session_id,
  26.            s.blocking_session,
  27.            s.sample_time
  28.     FROM dba_active_sess_history s
  29.     WHERE s.dbid = <db_id>
  30.     AND s.instance_number <inst_num>
  31.     AND s.snap_id BETWEEN <begin_snap> and <end_snap>
  32.     AND s.blocking_session IS NULL
  33.     AND s.event IN
  34.         ('enq: TX - row lock contention'
  35.         ,'enq: TM - contention'
  36.         ,'enq: UL - contention'
  37.         ,'enq: TX - allocate ITL entry')
  38.     AND EXISTS
  39.        (SELECT 'exists'
  40.         FROM DBA_HIST_ACTIVE_SESS_HISTORY bs
  41.         WHERE bs.dbid = <db_id>
  42.         AND bs.instance_number = <inst_num>
  43.         AND bs.snap_id BETWEEN <begin_snap> and <end_snap>
  44.         AND bs.blocking_session = s.session_id
  45.         AND bs.sample_time = s.sample_time
  46.         AND bs.blocking_session IS NOT NULL
  47.         AND bs.event IN
  48.             ('enq: TX - row lock contention'
  49.             ,'enq: TM - contention'
  50.             ,'enq: UL - contention'
  51.             ,'enq: TX - allocate ITL entry')
  52.        )
  53.    )
  54. CONNECT BY NOCYCLE PRIOR session_id = blocking_session
  55.                    AND PRIOR sample_time = sample_time
  56. ORDER BY level DESC, blocked_sid, sample_time;
复制代码

回复 只看该作者 道具 举报

7#
发表于 2017-8-16 16:19:16
16.6 "PL/SQL lock timer"等待事件
此类事件中，会话之间没有锁和交互。
通过调用USER_LOCK.SLEEP 或 DBMS_LOCK.SLEEP，会话在预先指定的时间长度中进行休眠。
不过它表现得就像会话被锁住了或存在性能问题一样。
"PL/SQL lock timer"等待的会话信息可以通过以下查询找到：
  1. SELECT
  2.    s.sid,
  3.    s.seconds_in_wait,
  4.    s.wait_class,
  5.    s.event,
  6.    s.p1text,
  7.    s.p1,
  8.    s.p1raw,
  9.    s.serial#,
  10.    s.program,
  11.    s.client_identifier,
  12.    s.terminal,
  13.    s.command,
  14.    ct.command_name,
  15.    s.service_name,
  16.    s.module,
  17.    s.action,
  18.    s.username,
  19.    s.machine,
  20.    s.sql_id,
  21.    st.sql_text, -- just the first 64 characters of the SQL text
  22.    st.hash_value
  23. FROM v$session s,
  24.      v$sqltext st,
  25.      v$sqlcommand ct
  26. WHERE st.sql_id (+) = s.sql_id
  27. AND st.piece (+) = 0
  28. AND ct.command_type (+) = s.command
  29. AND s.event = 'PL/SQL lock timer'
  30. 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）。
这个可以通过以下方式来完成：
  1. 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)".

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-5-17 17:55 , Processed in 0.051002 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569