首页 > Linux操作系统 > Linux操作系统 > oracle 锁简单记录

oracle 锁简单记录

原创 Linux操作系统 作者:sxitsxit 时间:2012-07-03 15:57:28 0 删除 编辑



1:表锁:锁表的结构  TM

2:行锁:锁表上的行  TX


v$mystat中的sid  v$session视图中的sid相等




DML statements automatically acquire both table-level locks and row-level locks


If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.




select * from v$lock_type  where type  in ('TM','TX') 


注意 Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE在日常中最常用的是TMTX 这两个锁

          v$lock中的lmode request、和block


SQL> select * from t;


     ID NAME              AGE     SALARY

---------- ---------- ---------- ----------

   1273999 a                  20       1000

   1273999 b                  30       1000


SQL> update t set name='c' where age=20;


1 row updated.


SQL> select distinct sid from v$mystat;








select * from v$lock where sid in (36)


找到 type TM 的这行数据,查看该行的ID1=75541  ,  然后再 结合 dba_objects 这个视图的 object_id=75541 ,找到被锁的表名

select * from dba_objects  where object_id=75541



在视图 v$transaction 中记录了与 TX 有关的信息





1select 查询不需要锁

2:锁定数据只有一种排他锁 exclusive (6 row)  ---TX

3:锁定元数据 mentadata 3 table---TM






2  RS row share

3  RX row exclusive

4  S share

5  SRXshare row exclusive

6  X exclusive


日常中 DML 操作,在表上都是加的 RX 3


A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.


注意: v$session 中的 sql_id 等于 v$sql中的sql_id


可以通过 v$session 中的 ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# 构造出 阻塞会话具体操作的行


SQL> select dbms_rowid.rowid_create(1,11756,4,16,2) from dual;






SQL> select * from 表名 where rowid='AAAC3sAAEAAAAAQAAC';


表名可以通过  ROW_WAIT_OBJ#  关联 dba_objects 视图中的  data_object_id  得到


Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE


A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Figure 13-3 is a hypothetical illustration of two transactions in a deadlock.

In Figure 13-3, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds without being terminated. However, each tries next to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held.

Figure 13-3 Two Transactions in a Deadlock

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

Deadlock Detection

Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes statement-level rollback. The statement rolled back is the one belonging to the transaction that detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting. 





来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

下一篇: undo简单介绍01
请登录后发表评论 登录


  • 博文量
  • 访问量