Oracle Lock Wait Analysis processing

1. The function and influence of the lock

Locks are one of the core mechanisms for Oracle to manage concurrent access to shared database resources and to prevent "mutual interference" between concurrent database transactions.

When the application system is complex and the volume of traffic is large, the lock wait phenomenon before the application process often occurs, which affects the normal operation of the system. Quickly locate the blocking process and analyze the blocking reason when the database has a lock wait.

2. Positioning lock Type

There are several types of database locks, each of which has a different impact on the business, and can be grouped into the following three categories:

DML lock

DDL lock

Internal lock and Latch lock

The following is an analysis of the positioning of each of the different types of locks, with various locking processes as follows:

2.1, DML Lock

Referencing a diagram in DSI405