请问对oracle的锁有研究的朋友们,oracle的死锁是怎样形成的?
根据我的经验,当一个用户从数据库的一个表中select一条记录(不加锁),然后修改(update语句完成),在未进行commit时,另外一个用户作同样的操作,update时会死锁。出现等待是正常,死锁究竟是怎样形成的?希望大家告诉我有关oracle锁的机制。
根据我的经验,当一个用户从数据库的一个表中select一条记录(不加锁),然后修改(update语句完成),在未进行commit时,另外一个用户作同样的操作,update时会死锁。出现等待是正常,死锁究竟是怎样形成的?希望大家告诉我有关oracle锁的机制。
解决方案 »
- 创建函数索引后列的对应关系
- MSSQL里的存储过程转到ORACLE中来
- oracle数据库实例sid可否修改
- 【慢】Imp导入忒慢!100M的.dmp文件,6千多行数据的一张表,两三个钟头也没完成!
- oracle建表问题求教,谢谢
- 存储过程被锁 如何解除呢
- 掉电,紧急求助!!
- 如何取得table1.field3 = table2.field3的字段,加上table1.field3 is null的字段?
- 小问题:在Oracle官方网站上下载的Oracle For Windows企业版
- 为什么我在9i客户端的企业管理器用表数据编辑器看到的中文是乱码,而用sql explorer和pl/sql developer中文都能正常显示?
- 送分了——寥寥几行sql语句,为什么会如此报错?盼高手作答,谢谢
- 如何直接调用ODBC驱动内的api函数
Oracle 关于锁的实现策略:
修改操作时,Oracle 引发行锁定, 它不会升级为块锁定,表锁定; 不会为了读取数据而使用锁定, 简单的读操作不会引发行锁定 ;
数据写不会阻碍数据读。让我再重复一次: 数据读不会被数据写
阻碍。在这点上Oracle 与其他大多数数据库存在根本差异;
只有在数据行已经被其他写操作锁定时,才会阻碍另一个数据写
操作。
如果你确认发生了死锁,查询V$_LOCK表. 找出锁:
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine,
a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null
and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
union
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine,
a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.username is not null
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value; 杀掉死锁进程:
alter system kill session 'sid, serial#'
这个操作很危险,最好由DBA完成.
Transaction 1
UPDATE emp SET sal = sal x 1.1 WHERE empno = 1000;
UPDATE emp SET mgr = 1342 WHERE empno = 2000;Transaction 2
UPDATE emp SET sal = sal x 1.1 WHERE empno = 2000;
UPDATE emp SET mgr = 1342 WHERE empno = 1000;ORA-00060:
Deadlock detected while waiting for resource Assuming the second update in Transaction 1 detects the deadlock, the Oracle server rolls back that statement and returns the message. Although the statement that caused the deadlock is rolled back, the transaction is not, and you should receive an ORA-00060 error. Your next action should be to roll back the remainder of the transaction.死锁发生时,oracle会自动检测,然后回滚一个transaction.
DML Locks
You can use DML locks to guarantee the integrity of data that is accessed concurrently by multiple users. Locks prevent destructive interference by simultaneous, conflicting DML and DDL operations. DML locks provide two levels of locks.
• A table-level lock (TM type) is set for any DML transaction that modifies a table:INSERT, UPDATE, DELETE, SELECT...FOR UPDATE, or LOCK TABLE. The table lock prevents DDL operations that conflict with the transaction.
• A row-level lock (TX type) is automatically acquired for each row that is modified by INSERT, UPDATE, DELETE, or SELECT...FOR UPDATE. The row level lock assures that no other user can modify the same row at the same time. Therefore,
there is no risk of a user modifying a row that is being modified and has not yet
been committed by another user.DDL Locks
A DDL lock protects the definition of a schema object while that object is acted upon or referred to by an ongoing DDL operation. The Oracle server automatically acquires a DDL lock to prevent any destructive interference with other DDL operations that might modify or reference the same schema object.