Oracle数据封锁机制邢海捷.PDF

Insert Picture Here Oracle 数据封锁机制 邢海捷 Agenda • 个人研究锁机制的过程 • 数据库多粒度封锁机制（数据库理论） • Oracle数据封锁机制 • Oracle数据锁的监控 • 锁阻塞场景演示 • 小结 2 个人研究锁机制的过程 • 刚工作时的经验教训(性能问题,行级锁与页锁， Sybase) • Oracle 经验(仍然碰到有些难以解释的问题) • 常规文档没有详细论述 • 网络上也缺少深入讨论 • 重温数据库教材 • 论坛讨论,出版物,用户组session 3 Oracle Locking a Brief Description • 为了确保并发用户在存取同一数据库对象时的正确性 ，数据库中引入了锁机制。 • Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. General Object Type Affected By Locks: • User objects, such as tables and rows (structures and data) • System objects not visible to users, such as shared data structures in the memory and data dictionary rows 5 Share Lock Mode Share Lock Mode 共享锁：若事务T对数据D加S锁，则其它事务只能对D加 S锁，而不能加X锁，直至T释放D上的S锁；一般要求 在读取数据前要向该数据加共享锁，所以共享锁又称 为读锁。 Allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource. 6 Exclusive Lock Mode Exclusive Lock Mode 排它锁：若事务T对数据D加X锁，则其它任何事务都不 能再对D加任何类型的锁，直至T释放D上的X锁；一 般要求在修改数据前要向该数据加排它锁，所以排它 锁又称为写锁。 Prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.