很久没写东西了,分享下小小的东西...翻译有点累..

概述:
*.Oracle使用latches保护数据结构的间歇的,短持续的访问,但是latch不适合保护一些持续时间相对比较长的Resource
  db tables.在之中情况下,就要使用lock
*.Lock允许并发的等待当前不可获取的Resource以Enqueue的形式来实现,这就避免了Latch所需要的Spin
*.Lock允许多个session共享一个Resource只要这些session的行为是兼容的

 

一.Lock Usage in Different Filed(各种常见的细粒度锁)

1.Transaction Lock(TX) and row-level lock:事务锁和行级锁

Oracle最自豪的就是row-level lock,当一个transaction更改了一行,该transaction 唯一标志就会以ITL entry的形式被记录在data block头
(其实transaction开始前应该在undo transaction table list中查找分配transaction的xid,同时将xid记录到data block中,以为itl的形式记录)
同时被更改的行头也被指向ITL,(lock bytes的形式),一旦所有更改被Commit/rollback,锁就会被释放掉
没有提交的transaction ITL entry,以及被reference的row header(lock bytes)构成了隐式的行级锁

当另外的transaction想更改同一行,它会在该data block中发现有uncommited的Transaction正在修改改行,那么这个transaction
就会wait(blocked阻塞),不是基于行级别锁,而是tx锁定的Blocking transaction

当Bloking transaction Commited/Rollback,TX锁就被释放,同时隐式的row-level lock也被释放,这样那个被TX block阻塞的tx就可以进行了.
值得注意的是Rollback to savepoint不能释放先前被锁定的TX的row-level lock(就是说rollbacl to savepoint不能释放row lock,也就是后面
被阻塞的TX也不能进行,只有rollback到最初状态或者commit后才能进行)

2.Buffer locks(Buffer 锁)
行级锁是保护数据的最小粒度,在TX存在的期间内与TX lock同时存在,Oracle同样需要获取短暂的block-level级别的锁来修改Buffer Cache中的buffer blocl
Buffer locks只提供简单的对buffer block的read/write操作权限,虽然很少提及,buffer block也是为了报数数据的完整性,对于performance有一定的帮助

 

3.Data Dictionary Locks(数据字典锁)
当Object 定义在被调用使用的时候时(p,c cursor,hash),必须保证数据的完整性,和有效性,以至于referenced objects不能被drop,在数据字典中的定义不能
被更改.
Dict locks必须获取在有依赖关系的SQL语句被parsed,executed,同时被保留在相应有依赖关系的的TX持续期间内(详见后面的lib cache locks,pins)

Dict cache lock由row cache enqueue locks保护
同时有依赖关系的SQL语句(lib cache locks),有依赖关系的SQL语句Reference Objects由lib cache pins保护
有依赖关系的TX由DML(DDL) lock保护
从逻辑意义上说,row cache lock,lib cache lock/pin,dml/ddl lock在依赖关系上是代码级别的隐式,而不是在结构上的显示
                 
                              
                                                                Library cache
                                                                P,C cursor/buckets
                                                                ____________                Writ to disk/Release locks/pins                              Disk
                                                                |___________|-------------------------------------------------------------|                                                              
                                                                |___________|                                                             |       
                                                                |___________|                                                             |      |___________|
 SQl Statement                                                  |___________|                                                             |      |___________|
|___________|                    Dict cache                     |___________|                                                             |----> |___________|
|___________|                  |___________|                    |___________|                                                                    |___________|
|___________|Parse/Execute     |___________| Reference Objects  |___________|-------------|get TX lock                                           |___________|
|___________|----------------->|___________|------------------->|___________|             |                                                      |___________|
|___________|                  |___________| Dict Definations   |___________|             |                                                      |___________|
|___________|                  |___________|                    |___________|             |                                              |------>|___________|
|___________|                  |___________|                                              |                                              |       |___________|
|___________|                  |___________|                                              |                                              |       |___________|
|___________|                  |___________|                                              |                                              |
|___________|                  |___________|       Reference SQL/buffer block             |           Buffer cache(DML/DDL) Locks        |
|___________|                  |___________|----------------------------------|           |           _____________                      |
                               |___________|                                  |           |           |___________|                      |
                                Row cache lock                                |           |           |___________|   Commit/Rollback    |
                                                                              |           |           |___________|----------------------|
                                                                              |           |           |___________|   Release Tx lock
                                                                              |           |---------->|___________|
                                                                              |                       |___________|
                                                                              |                       |___________|
                                                                              |                       |___________|
                                                                              |---------------------->|___________|
                                                                                                      |___________|
                                                                                                      |___________|