lock类型介绍
来源:互联网 发布:网络技术培训 编辑:程序博客网 时间:2024/06/04 19:45
排它锁(exclusive locks,即X锁):当数据对象被加上排它锁时,其他的事务不能对它读取和修改
共享锁(share locks,即S锁):加了共享锁的数据对象可以被其他事务读取,但不能修改
表级锁TM有5种类型:S,X,RS(SS),RX(SX),SSX(SRX).
Value Name(s) Table method (TM lock)
0 No lock n/a
1 Null lock (NL) Used during some parallel DML operations (e.g. update) by the pX slaves while the QC is holding an exclusive lock.
2 Sub-share (SS) Until 9.2.0.5/6 "select for update"
Row-share (RS) Since 9.2.0.1/2 used at opposite end of RI during DML
Lock table in row share mode
Lock table in share update mode
3 Sub-exclusive(SX) Update (also "select for update" from 9.2.0.5/6)
Row-exclusive(RX) Lock table in row exclusive mode
Since 11.1 used at opposite end of RI during DML
4 Share (S) Lock table in share mode
Can appear during parallel DML with id2 = 1, in the PX slave sessions
Common symptom of "foreign key locking" (missing index) problem
5 share sub exclusive (SSX) Lock table in share row exclusive mode
share row exclusive (SRX) Less common symptom of "foreign key locking" but likely to be more frequent if the FK constraint is defined with "on delete cascade."
6 Exclusive (X) Lock table in exclusive mode
S:
仅允许其它事务查询被锁定的表,Lock table in share mode;
防止其它事务更新该表或执行下面的语句:
lock table in share row excluesive mode
lock table in row exclusive mode
X:
允许加排它锁的事务独自控制对表的写权限,lock table in exclusive mode;
在一个表中只能有一个事务对该表实行排它锁,排它锁仅允许其它的事务查询该表,禁止其它事务做任何DML操作或在该表加任何其他类型的锁
RS(SS):
有时也称subshare table lock,即SS,需要该事务再被锁定行的表上用update形式加锁,lock table in row share mode,Lock table in share update mode;
RX(SX):
有时也称subexclusive table lock,简称SX,子排它锁,通常需要事务拥有的锁在表上被更新一行或多行,Lock table in row exclusive mode;
防止以下操作
lock table in share mode
lock table in share exclusive mode
lock table in exclusive mode
SRX(SSX)
有时也称share subexclusive table lock,简称SSX,lock table in share row exclusive mode
防止以下操作
lock table in share mode
lock table in share row exclusive mode
lock table in row exclusive mode
lock in exclusive mode
事务锁TX:当oracle检测到死锁产生时,中断并回滚死锁相关语句的执行,报ORA-00060的记录并记录在数据库的日志文件alert_sid.log中,同时也会在user_dump_dest下产生了一个详情的跟踪文件。
v$session.sid,v$session.serial#:表示会话信息
v$session.program:表示会话的应用程序信息
v$session.row_wait_obj#:表示等待的对象,与dba_objects.object_id一致
v$session_wait.sid:表示持有锁的会话信息
v$session_wait.seconds_in_wait:表示等待持续的时间信息
v$session_wait.event:表示会话等待的事件
v$lock.sid:表时持有锁的会话信息==>dba_locks.session_id
v$lock.type:表示锁的类型,值包括TM和TX等==>dba_locks.lock_type
v$lock.ID1:表示锁的对象标识==>dba_locks.lock_ID1
v$lock.lmode:表示会话等待的锁模式 0-6==>dba_locks.mode_held
v$lock.request:表示进程等待的锁模式 0-6==>dba_locks.mode_requested
v$locked_object.xidusn,v$locked_object.xidslot,v$locked_object.xidsqn:表示回滚段信息==>v$transaction.xidusn,v$transaction.xidslot,v$transaction.xidsqn
v$locked_object.ojbect_id:表示被锁对象标识
v$locked_object.session_id:表示持有锁的会话信息
v$locked_object.locked_mode:表示会话等待的锁模式的信息和v$lock.lmode
ompatible SS(RS) SX(RX) S SSX(SRX) X
SS,RS yes yes yes yes no
SX,RX yes yes no no no
S yes no yes no no
SSX, SRX yes no no no no
X no no no no no
SQL Statement Row Locks Table Lock Mode RS RX S SRX X
SELECT … FROM table... — none Y Y Y Y Y
INSERT INTO table … Yes SX Y Y N N N
UPDATE table … Yes SX Y* Y* N N N
MERGE INTO table … Yes SX Y Y N N N
DELETE FROM table … Yes SX Y* Y* N N N
SELECT … FROM table FOR UPDATE OF … Yes SX Y* Y* N N N
LOCK TABLE table IN … —
ROW SHARE MODE SS Y Y Y Y N
ROW EXCLUSIVE MODE SX Y Y N N N
SHARE MODE S Y N Y N N
SHARE ROW EXCLUSIVE MODE SSX Y N N N N
EXCLUSIVE MODE X N N N N N
* Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.
共享锁(share locks,即S锁):加了共享锁的数据对象可以被其他事务读取,但不能修改
表级锁TM有5种类型:S,X,RS(SS),RX(SX),SSX(SRX).
Value Name(s) Table method (TM lock)
0 No lock n/a
1 Null lock (NL) Used during some parallel DML operations (e.g. update) by the pX slaves while the QC is holding an exclusive lock.
2 Sub-share (SS) Until 9.2.0.5/6 "select for update"
Row-share (RS) Since 9.2.0.1/2 used at opposite end of RI during DML
Lock table in row share mode
Lock table in share update mode
3 Sub-exclusive(SX) Update (also "select for update" from 9.2.0.5/6)
Row-exclusive(RX) Lock table in row exclusive mode
Since 11.1 used at opposite end of RI during DML
4 Share (S) Lock table in share mode
Can appear during parallel DML with id2 = 1, in the PX slave sessions
Common symptom of "foreign key locking" (missing index) problem
5 share sub exclusive (SSX) Lock table in share row exclusive mode
share row exclusive (SRX) Less common symptom of "foreign key locking" but likely to be more frequent if the FK constraint is defined with "on delete cascade."
6 Exclusive (X) Lock table in exclusive mode
S:
仅允许其它事务查询被锁定的表,Lock table in share mode;
防止其它事务更新该表或执行下面的语句:
lock table in share row excluesive mode
lock table in row exclusive mode
X:
允许加排它锁的事务独自控制对表的写权限,lock table in exclusive mode;
在一个表中只能有一个事务对该表实行排它锁,排它锁仅允许其它的事务查询该表,禁止其它事务做任何DML操作或在该表加任何其他类型的锁
RS(SS):
有时也称subshare table lock,即SS,需要该事务再被锁定行的表上用update形式加锁,lock table in row share mode,Lock table in share update mode;
RX(SX):
有时也称subexclusive table lock,简称SX,子排它锁,通常需要事务拥有的锁在表上被更新一行或多行,Lock table in row exclusive mode;
防止以下操作
lock table in share mode
lock table in share exclusive mode
lock table in exclusive mode
SRX(SSX)
有时也称share subexclusive table lock,简称SSX,lock table in share row exclusive mode
防止以下操作
lock table in share mode
lock table in share row exclusive mode
lock table in row exclusive mode
lock in exclusive mode
事务锁TX:当oracle检测到死锁产生时,中断并回滚死锁相关语句的执行,报ORA-00060的记录并记录在数据库的日志文件alert_sid.log中,同时也会在user_dump_dest下产生了一个详情的跟踪文件。
v$session.sid,v$session.serial#:表示会话信息
v$session.program:表示会话的应用程序信息
v$session.row_wait_obj#:表示等待的对象,与dba_objects.object_id一致
v$session_wait.sid:表示持有锁的会话信息
v$session_wait.seconds_in_wait:表示等待持续的时间信息
v$session_wait.event:表示会话等待的事件
v$lock.sid:表时持有锁的会话信息==>dba_locks.session_id
v$lock.type:表示锁的类型,值包括TM和TX等==>dba_locks.lock_type
v$lock.ID1:表示锁的对象标识==>dba_locks.lock_ID1
v$lock.lmode:表示会话等待的锁模式 0-6==>dba_locks.mode_held
v$lock.request:表示进程等待的锁模式 0-6==>dba_locks.mode_requested
v$locked_object.xidusn,v$locked_object.xidslot,v$locked_object.xidsqn:表示回滚段信息==>v$transaction.xidusn,v$transaction.xidslot,v$transaction.xidsqn
v$locked_object.ojbect_id:表示被锁对象标识
v$locked_object.session_id:表示持有锁的会话信息
v$locked_object.locked_mode:表示会话等待的锁模式的信息和v$lock.lmode
ompatible SS(RS) SX(RX) S SSX(SRX) X
SS,RS yes yes yes yes no
SX,RX yes yes no no no
S yes no yes no no
SSX, SRX yes no no no no
X no no no no no
SQL Statement Row Locks Table Lock Mode RS RX S SRX X
SELECT … FROM table... — none Y Y Y Y Y
INSERT INTO table … Yes SX Y Y N N N
UPDATE table … Yes SX Y* Y* N N N
MERGE INTO table … Yes SX Y Y N N N
DELETE FROM table … Yes SX Y* Y* N N N
SELECT … FROM table FOR UPDATE OF … Yes SX Y* Y* N N N
LOCK TABLE table IN … —
ROW SHARE MODE SS Y Y Y Y N
ROW EXCLUSIVE MODE SX Y Y N N N
SHARE MODE S Y N Y N N
SHARE ROW EXCLUSIVE MODE SSX Y N N N N
EXCLUSIVE MODE X N N N N N
* Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.
0 0
- lock类型介绍
- Java中的lock类型
- Spin lock 介绍
- 介绍D3DPOOL和Lock
- 介绍D3DPOOL和Lock
- JPA lock 官方介绍
- AbstractQueuedSynchronizer的lock原理介绍
- Lock接口API简单介绍
- java多线程之Lock介绍
- C++11多线程之lock类型
- c# lock,Monitor(介绍) 多线程并发控制
- 介绍spin-lock的一篇杂文
- W7100A闪存锁(Flash Lock)功能介绍
- Mutex的lock(), unlock(), tryLock()函数介绍
- V$LOCK视图的介绍和使用
- Mutex的lock(), unlock(), tryLock()函数介绍
- Lock接口的介绍及使用
- LOCK
- 博弈论中的威佐夫博奕
- 单线程环境中对象大小可变的内存池
- node.js下mongoose简单操作实例
- 添加完支付宝第三方的库喜欢出错
- Java中如何利用Selenium获取元素分析网页内容
- lock类型介绍
- Spring RESTful服务接收和返回JSON最佳实践
- ServU 教程11.1.0.7使用教程
- 构造函数、析构函数、拷贝构造函数
- 位操作习题1
- 视频压缩编码和音频压缩编码的基本原理
- shell脚本显示字体颜色
- Tomcat__实现简单的登陆功能
- NAT专题