深入理解Oracle DB的锁和闩

标签：事务数据库 DB 查询深入 SQL Oracle 数据可以

1. 引言

本文深入介绍Oracle DB的锁和闩。

2. Oracle DB 锁的基本概念

2.1 定义与作用

锁是 Oracle 数据库用于控制并发访问的一种机制。它用于防止多个事务同时对同一数据进行不一致的操作，确保数据的完整性和一致性。例如，当一个事务正在更新一行数据时，通过加锁可以防止其他事务同时对这行数据进行更新或删除操作。

2.2 锁的类型

共享锁（Share Lock，S Lock）： 允许同时有多个事务对同一数据进行读取操作。例如，多个用户可以同时查询同一张表中的数据，这时可以对表加上共享锁。共享锁之间是兼容的，即一个事务对数据加上共享锁后，其他事务仍然可以对该数据加上共享锁来进行读取。
排他锁（Exclusive Lock，X Lock）： 当一个事务对数据加上排他锁后，其他事务既不能对该数据进行读取操作，也不能进行写入操作。排他锁主要用于数据的修改操作，如插入、更新和删除，以确保在操作过程中数据的完整性。例如，一个事务正在更新表中的一行数据，就会对该行数据加上排他锁。
行级锁（Row - level Lock）： 这种锁是对表中的行进行锁定。例如，在一个事务更新表中的某一行时，只会锁定这一行，而不会影响表中的其他行，这样可以提高并发性能，使其他事务能够同时访问表中的其他行。
表级锁（Table - level Lock）： 是对整个表进行锁定。表级锁可以是共享表级锁或排他表级锁。当需要对整个表进行批量操作（如删除所有行）或者需要确保整个表的一致性时，可以使用表级锁。不过表级锁会限制其他事务对表的访问，可能会影响并发性能。

3. Oracle DB 锁的工作原理

3.1 锁的获取与释放

当一个事务需要访问数据并且需要保证数据的一致性时，它会请求相应的锁。例如，当执行一个UPDATE语句时，事务会自动请求排他行级锁。锁会一直保持到事务结束（提交或回滚）。在事务提交或回滚后，所获取的锁会自动释放。

3.2 锁的兼容性矩阵

共享锁和排他锁之间是不兼容的，即如果一个数据已经被加上排他锁，其他事务不能再对其加共享锁或排他锁；而共享锁之间是兼容的，多个事务可以同时对同一数据加共享锁。这种兼容性规则确保了在多事务并发访问数据时，数据的读写操作能够按照合理的顺序进行。

3.3 死锁处理

死锁是指两个或多个事务相互等待对方释放锁的情况。例如，事务 A 锁定了资源 S 并等待获取资源 T，而事务 B 锁定了资源 T 并等待获取资源 S。Oracle 数据库有自动检测死锁的机制，当检测到死锁时，会选择一个事务作为牺牲品进行回滚，以解除死锁状态，让其他事务能够继续执行。
在这里插入图片描述

4. Oracle DB 闩（Latch）的基本概念

4.1 定义与作用

闩是一种比锁更轻量级的保护机制，用于保护 Oracle 数据库内部的内存结构。它主要用于控制对共享内存资源（如缓存、数据结构等）的并发访问。例如，在数据库缓存（Buffer Cache）中，多个进程可能会同时访问缓存中的数据块，闩可以确保这些访问是有序的，防止数据不一致。

4.2 闩与锁的区别

锁主要用于保护数据库中的数据对象（如表、行），用于事务之间的并发控制；而闩主要用于保护数据库内部的系统资源和内存结构，是在更底层的操作中使用。锁的操作通常是由用户事务发起的，并且可以在事务的整个生命周期内持续存在；而闩的持有时间通常很短，只是在对内部资源进行短暂操作时使用。

4.3 Oracle DB 闩的工作原理

4.3.1 闩的获取与释放

当一个数据库进程需要访问受闩保护的内存结构时，它会尝试获取闩。如果闩已经被其他进程占用，该进程会等待（短暂等待，通常是自旋等待），直到闩被释放。一旦获取闩，进程就可以对受保护的内存结构进行操作，操作完成后会立即释放闩，以允许其他进程访问。

4.3.2 闩的类型和应用场景

共享闩（Share Latch）： 用于多个进程可以同时访问但需要协调访问顺序的内存结构。例如，在数据库的库缓存（Library Cache）中，多个进程可能需要查询相同的 SQL 语句解析结果，共享闩可以确保这些进程能够安全地共享这些资源。
排他闩（Exclusive Latch）：用于对内存结构进行独占式操作的情况。例如，当需要对缓存中的数据块进行更新（如从磁盘读取新的数据块替换缓存中的旧数据块）时，会使用排他闩来防止其他进程在更新过程中访问该数据块。

5. 监控和管理数据库的锁和闩

5.1 使用动态性能视图进行监控

V L O C K 视图 V LOCK 视图 V LOCK视图VLOCK视图提供了数据库中当前持有的锁的信息。它包含了诸如会话 ID（SID）、锁类型、锁模式、被锁定对象的 ID 等重要信息。例如，可以通过查询这个视图来了解哪些会话正在持有锁，以及这些锁是共享锁还是排他锁。
示例：

SELECT SID, TYPE, LMODE, ID1, ID2
FROM V$LOCK;

这个查询会返回每个锁的会话 ID、锁类型（如TM代表表级锁，TX代表事务锁等）、锁模式（数字表示，如 0 表示无锁，1 表示空共享锁，2 表示行共享锁，3 表示排他锁等）以及被锁定对象的标识符（ID1和ID2）。通过这些信息，可以分析锁的分布情况和锁的严重程度。

V L A T C H 视图 V LATCH 视图 V LATCH视图VLATCH视图用于查看闩的相关信息。它展示了闩的名称、获取次数、等待次数等内容。这些信息对于了解数据库内部资源的并发访问情况非常重要。
示例：

SELECT NAME, GETS, MISSES, SLEEPS
FROM V$LATCH;

其中GETS表示闩的获取次数，MISSES表示获取闩时未成功（需要等待）的次数，SLEEPS表示由于等待闩而导致进程睡眠的次数。通过这些统计信息，可以判断是否存在闩争用问题，以及哪些闩可能需要进一步优化。

5.2 使用 Oracle Enterprise Manager（OEM）进行管理和监控

图形化界面操作
OEM 提供了一个直观的图形化界面来管理和监控数据库。在 OEM 中，可以方便地查看锁和闩的信息。例如，可以通过导航到 “性能” - > “并发” - > “锁” 来查看当前数据库中的锁活动情况(不同版本DB 会有差异)。在这里，可以看到锁的持有者、被锁定的对象、锁等待情况等详细信息，并且可以通过图形化的方式直观地展示锁的冲突和等待链。
对于闩的监控，同样可以在 OEM 的相关性能监控模块中找到。可以查看闩的使用率、等待时间等统计数据的图表，以便及时发现潜在的性能问题。
设置警报和阈值
OEM 允许设置警报来提醒管理员关于锁和闩的异常情况。例如，可以设置当锁等待时间超过一定阈值（如 10 秒）或者闩的等待次数达到某个数值时，自动发送警报通知。通过这种方式，可以及时发现并处理可能影响数据库性能的问题。

5.3 SQL Trace 和 TKPROF 工具的使用

SQL Trace 原理
SQL Trace 是 Oracle 提供的一个工具，用于跟踪数据库中的 SQL 语句执行情况。它可以记录 SQL 语句的执行计划、资源使用情况（包括锁和闩的使用）等详细信息。当启用 SQL Trace 后，数据库会在后台记录每个被跟踪的 SQL 语句相关的信息。
TKPROF 工具分析
TKPROF 是一个用于分析 SQL Trace 输出文件的工具。通过运行 TKPROF 工具，可以得到关于 SQL 语句的详细报告，包括锁等待时间、闩等待时间等信息。例如，可以通过以下步骤使用这些工具：
首先，在数据库中启用 SQL Trace 来跟踪特定会话或整个系统的 SQL 语句执行情况：
ALTER SESSION SET SQL_TRACE = TRUE;
然后，让数据库执行相关操作，之后使用 TKPROF 工具分析生成的跟踪文件（假设跟踪文件名为trace_file.trc）：
tkprof trace_file.trc output_file.txt
在生成的output_file.txt报告中，可以查找与锁和闩相关的部分，如WAIT #n:部分会显示等待事件的详细信息，包括锁等待和闩等待事件，通过分析这些信息可以确定哪些 SQL 语句导致了锁和闩的问题。

5.4 使用数据库内置的包进行锁管理

DBMS_LOCK 包
DBMS_LOCK包提供了一系列用于显式地请求、释放和转换锁的过程和函数。例如，可以使用REQUEST函数来请求一个特定类型的锁，使用RELEASE函数来释放一个锁。
示例：

DECLARE
    lock_handle VARCHAR2(128);
    return_code NUMBER;
BEGIN
    DBMS_LOCK.ALLOCATE_UNIQUE('my_lock_name', lock_handle);
    return_code := DBMS_LOCK.REQUEST(lock_handle, DBMS_LOCK.X_MODE, 0, TRUE, 0);
    -- 执行需要排他锁保护的操作
    DBMS_LOCK.RELEASE(lock_handle);
END;

这个示例代码首先为一个自定义的锁名称分配一个唯一的锁句柄，然后使用REQUEST函数请求一个排他锁（X_MODE），在执行完需要保护的操作后，使用RELEASE函数释放锁。这种方式可以在应用程序中对锁进行更精细的控制，特别是在复杂的并发场景中。

6. 如何解决数据库中的锁和闩争用问题

6.1 优化事务设计

减少事务的持有时间
事务持有锁的时间越长，其他事务等待锁的可能性就越大。因此，应尽量使事务中的操作简洁高效，避免在事务中包含不必要的长时间操作。
例如：如果一个事务需要更新数据库中的大量数据，不要在一个事务中一次性更新所有数据，可以将更新操作拆分成多个小事务，每次更新一部分数据，这样可以减少每个事务持有锁的时间，降低锁争用的概率。例如，将一个更新 100 万行数据的事务拆分成 100 个每次更新 1 万行数据的事务。
事务隔离级别调整
不同的事务隔离级别对锁的使用方式和范围有所不同。例如，在Serializable隔离级别下，会对读取的数据加共享锁，并且会一直持有到事务结束，这可能导致较高的锁争用。如果应用程序可以接受一定程度的数据不一致性（如允许读取到未提交的数据），可以适当降低隔离级别来减少锁的使用。
例如：在一个主要是查询操作，对数据一致性要求不是极高的系统中，可以将事务隔离级别从Serializable调整为Read Committed。在Read Committed隔离级别下，事务读取数据时不会对数据加锁（读取的是已经提交的数据），只有在更新数据时才会加排他锁，这样可以有效减少锁的使用和争用。

6.2 优化 SQL 语句和查询执行计划

索引优化
合理的索引可以加快 SQL 语句的执行速度，减少事务的执行时间，从而间接减少锁的持有时间。例如，在一个经常用于查询和更新的表中，如果在查询条件和更新条件涉及的列上建立索引，数据库引擎在执行操作时可以更快地定位到需要的数据，减少全表扫描的可能性，因为全表扫描可能会对整个表加锁，导致锁争用。
例：对于一个包含customer_id、customer_name和customer_order_date等列的customers_orders表，如果经常根据customer_id进行查询和更新操作，如SELECT * FROM customers_orders WHERE customer_id = 123或UPDATE customers_orders SET customer_order_date = SYSDATE WHERE customer_id = 123，可以在customer_id列上建立索引，这样数据库在执行这些操作时能够更快地找到相关数据，减少锁的使用范围和时间。
避免复杂的关联查询和子查询
复杂的查询可能会导致数据库执行计划复杂，涉及多个表的锁操作。例如，一个包含多个嵌套子查询和多表连接的查询可能会导致数据库在执行过程中对多个表加锁，增加锁争用的风险。尽量简化查询，减少不必要的表连接和子查询，可以降低锁的使用。
例：如果有一个复杂的查询，如SELECT * FROM table1 JOIN (SELECT * FROM table2 WHERE condition) subquery ON table1.id = subquery.id，可以考虑将子查询转换为临时表或者视图，或者重新设计查询逻辑，减少嵌套层次和表连接的复杂性，从而减少锁的使用。

6.3 调整数据库对象结构和参数

分区表的使用
对于大型表，使用分区表可以将数据划分到不同的分区，在进行操作时可以只对相关分区加锁，而不是对整个表加锁。这样可以提高并发性能，减少锁争用。
例：对于一个存储销售订单数据的大型表，按照日期范围进行分区，如每个月的数据为一个分区。当需要对某个月的订单数据进行更新或查询时，数据库只会对相应月份的分区加锁，而不会影响其他月份分区的数据访问，从而有效减少锁争用。
调整闩相关参数
闩的性能和行为可以通过一些数据库参数进行调整。例如，通过调整闩等待时间相关参数，可以控制数据库进程在获取闩时等待的时间，避免进程长时间等待闩而导致性能下降。
例：在 Oracle 数据库中，可以通过参数_spin_count来调整闩的自旋等待次数。适当增加这个参数的值可以减少进程在等待闩时进入睡眠状态的次数，提高闩的获取效率，但也可能会增加 CPU 的开销。需要根据实际情况进行调整，如在一个 CPU 资源丰富的系统中，可以适当增加_spin_count来提高闩的性能。

6.4 应用程序层面的优化

连接池优化
在多用户应用程序中，连接池用于管理数据库连接。如果连接池配置不合理，可能会导致过多的连接同时请求锁和闩。例如，连接池中的连接数过多可能会导致数据库负载过重，增加锁和闩争用的可能性；连接数过少则可能会导致应用程序等待连接，影响性能。
例：根据应用程序的并发访问量和数据库服务器的性能，合理配置连接池的大小。可以通过性能测试来确定最佳的连接池大小，例如，在一个 Web 应用程序中，开始时可以设置连接池大小为 100，通过性能测试工具模拟并发访问，观察数据库的负载、锁和闩争用情况以及应用程序的响应时间，根据测试结果逐步调整连接池大小，找到一个既能满足并发访问需求，又能减少锁和闩争用的合适值。
缓存策略的使用
在应用程序中使用缓存可以减少对数据库的直接访问，从而减少锁和闩的使用。例如，对于一些频繁查询但很少更新的数据，可以将其缓存到应用程序的内存中，当需要访问这些数据时，先从缓存中获取，只有在缓存中不存在或者数据已过期时才访问数据库。
例：在一个电商应用程序中，商品分类信息通常很少更新，可以将商品分类信息缓存到应用程序的内存中。当用户浏览商品分类时，直接从缓存中获取分类信息，而不需要对数据库中的分类表进行查询和加锁操作，这样可以有效减少锁争用，提高应用程序的性能。

7. 后记

锁问题一直是事务处理和高并发编程的核心问题，大家一定要搞透彻明白才能写出高效的代码。

码字不易，宝贵经验分享不易，请各位支持原创，转载注明出处，多多关注作者，后续不定期分享DB基本知识和排障案例及经验、性能调优等。

标签：事务,数据库,DB,查询,深入,SQL,Oracle,数据,可以
From： https://blog.csdn.net/qq_45732829/article/details/144112704