SoFunction
Updated on 2025-04-14

Oracle lock table solution and best practices to avoid lock table problem

Background introduction

In Oracle databases, it is believed that everyone is familiar with lock tables or lock timeouts, which is a common problem, especially when executing DML (data operation language) statements. When a session locks a table or row but does not commit a transaction, other sessions may time out because they wait for locking the resource. This situation not only affects database performance, but may also cause application exceptions (: Lock wait timeout exceeded).

This article will provide details on how to resolve lock table problems and how to find SQL statements that cause lock tables, and provide best practices to avoid lock table problems.

Reason for locking table

  1. Exclusive blockade mechanism: Oracle uses exclusive blocking mechanism to ensure data consistency. When a session modifies data, it is locked until the transaction is committed or rolled back.
  2. Long-running SQL statements: Some SQL statements may run for a long time due to performance issues or other reasons, resulting in lock resources being occupied.
  3. High concurrency scenarios: In a high concurrency environment, multiple sessions access the same data at the same time may lead to lock competition, causing deadlocks.

Solution to lock table

Temporary solution

  • Find out the sessions that are competing for lock resources
SELECT L.SESSION_ID, #, L.LOCKED_MODE AS "lock mode",       L.ORACLE_USERNAME AS "owner", L.OS_USER_NAME AS "Login system username", 
        AS "System Name",  AS "End Username", 
       O.OBJECT_NAME AS "Locked table object name", S.LOGON_TIME AS "Login database time"
  FROM V$LOCKED_OBJECT L
  INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
  INNER JOIN V$SESSION S ON  = L.SESSION_ID;
  • SQL forced ending session
ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';

Example

Assumptionsession1Modified a piece of data but did not submit the transaction.session2Querying the record that has not submitted a transaction will be blocked.

  • Query session information for uncommitted transactions
SELECT L.SESSION_ID, #, L.LOCKED_MODE AS "lock mode",       L.ORACLE_USERNAME AS "owner", L.OS_USER_NAME AS "Login system username", 
        AS "System Name",  AS "End Username", 
       O.OBJECT_NAME AS "Locked table object name", S.LOGON_TIME AS "Login database time"
  FROM V$LOCKED_OBJECT L
  INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
  INNER JOIN V$SESSION S ON  = L.SESSION_ID;


 SESSION_ID	SERIAL# Lock mode Owner Login system username System name Terminal username Locked table object name Login database time----------  ------- ----- ------ ------------- ----- --------- --------- ------------
29	84	3 IN	test	WORKGROUP\LA...	LAPTOP-9FDC2903	LIN_USER	2023/2/26 11:08:08
  • Forced endsession1
ALTER SYSTEM KILL SESSION '29, 84';
  • verifysession2Implementation status
    • Forced endsession1back,session2The wait will be terminated immediately and executed.

Find locked objects

  • Query the number of locked objects
SELECT COUNT(1) FROM V$LOCKED_OBJECT;
  • Query the locked object
SELECT , B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
 WHERE B.OBJECT_ID = A.OBJECT_ID;
  • Query the connection of the locked object
SELECT , , , T2.LOGON_TIME
  FROM V$LOCKED_OBJECT T1, V$SESSION T2
 WHERE T1.SESSION_ID = 
 ORDER BY T2.LOGON_TIME;
  • Close the locked object connection
ALTER SYSTEM KILL SESSION '253, 9542';

Check the lock table status in the current system

  • Query all locked objects
SELECT * FROM V$LOCKED_OBJECT;
  • Query the detailed lock table situation
SELECT , #, LO.ORACLE_USERNAME, LO.OS_USER_NAME, AO.OBJECT_NAME, LO.LOCKED_MODE
  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS, V$PROCESS P
 WHERE AO.OBJECT_ID = LO.OBJECT_ID
   AND LO.SESSION_ID = ;

Find SQL statements that cause lock tables

  • Query SQL statement causing lock table
SELECT L.SESSION_ID SID, #, L.LOCKED_MODE, L.ORACLE_USERNAME, #, L.OS_USER_NAME, , , A.SQL_TEXT, 
  FROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT L
 WHERE L.SESSION_ID = 
   AND S.PREV_SQL_ADDR = 
 ORDER BY SID, #;
  • View all blocked sessions
SET LINE 200;
COL TERMINAL FORMAT A10;
COL PROGRAM FORMAT A20;
COL USERNAME FORMAT A10;
COL MACHINE FORMAT A10;
COL SQL_TEXT FORMAT A40;
SELECT , #, , , , , , , , A.SECONDS_IN_WAIT, B.SQL_TEXT
  FROM V$SESSION A, V$SQL B
 WHERE B.SQL_ID = A.SQL_ID
   AND (A.BLOCKING_INSTANCE IS NOT NULL AND A.BLOCKING_SESSION IS NOT NULL);
  • Show blocking tree structure
WITH lk AS (
  SELECT BLOCKING_INSTANCE || '.' || BLOCKING_SESSION AS blocker, INST_ID || '.' || SID AS waiter
    FROM GV$SESSION
   WHERE BLOCKING_INSTANCE IS NOT NULL AND BLOCKING_SESSION IS NOT NULL
)
SELECT LPAD('  ', 2 * (LEVEL - 1)) || WAITER LOCK_TREE
  FROM (
    SELECT * FROM lk
    UNION ALL
    SELECT DISTINCT 'root', BLOCKER FROM lk
    WHERE BLOCKER NOT IN (SELECT WAITER FROM lk)
  )
CONNECT BY PRIOR WAITER = BLOCKER
START WITH BLOCKER = 'root';
  • Show the blocking tree structure, output blocking statements, blocked statements, and give killing statements
WITH lk AS (
  SELECT A.BLOCKING_INSTANCE || '.' || A.BLOCKING_SESSION AS blocker,
         A.INST_ID || '.' ||  AS waiter,
         (SELECT B.SQL_TEXT || '  ALTER SYSTEM KILL SESSION ''' ||  || ', ' || # || ''''
            FROM GV$SQLAREA B, GV$SESSION C
           WHERE A.BLOCKING_INSTANCE = C.INST_ID
             AND  = A.BLOCKING_SESSION
             AND (C.SQL_ID = B.SQL_ID OR C.PREV_SQL_ID = B.SQL_ID)) AS kill_block_sql,
         (SELECT B.SQL_TEXT || '  ALTER SYSTEM KILL SESSION ''' ||  || ', ' || # || ''''
            FROM GV$SQLAREA B
           WHERE A.INST_ID = B.INST_ID
             AND A.SQL_ID = B.SQL_ID) AS kill_waiter_sql
    FROM GV$SESSION A
   WHERE A.BLOCKING_INSTANCE IS NOT NULL AND A.BLOCKING_SESSION IS NOT NULL
)
SELECT LPAD('  ', 2 * (LEVEL - 1)) || WAITER || '  ' || KILL_WAITER_SQL LOCK_TREE
  FROM (
    SELECT BLOCKER, WAITER, KILL_WAITER_SQL FROM lk
    UNION ALL
    SELECT DISTINCT 'root', BLOCKER, KILL_BLOCK_SQL FROM lk
    WHERE BLOCKER NOT IN (SELECT WAITER FROM lk)
  )
CONNECT BY PRIOR WAITER = BLOCKER
START WITH BLOCKER = 'root';
  • Show blocking relationships directly
COL BLOCK_MSG FOR A80
SELECT  || ' (''' ||  || ',' || # || ''') is blocking ' ||  BLOCK_MSG
  FROM V$LOCK A, V$LOCK B, V$SESSION C
 WHERE A.ID1 = B.ID1
   AND A.ID2 = B.ID2
   AND  > 0
   AND  <> 
   AND  = ;

Best practices to avoid lock table problems

1. Optimize SQL statements

  • Reduce lock range: Try to use row-level locks instead of table-level locks. For example, useSELECT ... FOR UPDATEWhen, only the rows that need to be updated are locked.
  • Avoid long-running transactions: Ensure that transactions are as short as possible, commit or roll back transactions as soon as possible, and reduce lock holding time.
  • Batch processing: For operations with large amounts of data, consider batch processing to reduce the duration of a single transaction and the holding time of the lock.

2. Use the appropriate isolation level

  • Adjust isolation level: Select the appropriate isolation level according to application needs. For example, useREAD COMMITTEDInstead ofSERIALIZABLE, to reduce competition for locks.
  • Avoid unnecessary locks: In some cases, it can be usedNOLOCKPrompts to avoid locks during read operations, but this may cause dirty reads.

3. Optimize index

  • Create the appropriate index: Ensure that the frequently queried columns have appropriate indexes to reduce competition for full table scans and locks.
  • Maintain index: Regularly rebuild and reorganize the index to maintain its efficiency.

4. Use partition tables

  • Partition table: For large tables, partitioning technology can be used to reduce competition for locks. A partition table can divide the data into multiple parts, each part can operate independently, thereby reducing the impact of locks.

5. Optimize application logic

  • Reduce concurrent conflicts: When designing application logic, minimize concurrent access to the same data. For example, access to shared resources is serialized by using queues or other mechanisms.
  • Use optimistic lock: For some non-critical operations, optimistic locks (such as version number control) can be used instead of pessimistic locks to reduce the competition for locks.

6. Monitoring and tuning

  • Monitor lock situation: Regularly monitor the lock situation in the database and useV$LOCKED_OBJECT、V$SESSIONandV$SQLAREASuit view to identify potential lock problems.
  • Set timeout: Set a reasonable lock waiting timeout for the session to prevent a certain session from occupying lock resources for a long time. Can be passedALTER SYSTEM SET LOCK_TIMEOUT = <seconds>To set it up.

7. Use database features

  • Flashback technology: Utilize Oracle's flashback technology (such as Flashback Query) to recover data instead of relying on complex transaction rollbacks.
  • Online redefinition: Use Online Redefinition to modify the table structure without affecting existing transactions.

8. Transaction Management

  • Minimize transaction size: Try to split large transactions into multiple small transactions to reduce the lock holding time.
  • Use save points: Use savepoints (SAVEPOINT) in long transactions so that when an error occurs, you can roll back to a specific point, rather than the entire transaction.

9. Database configuration

  • Adjust parameters: Adjust database parameters according to actual conditions, such asUNDO_RETENTION、DB_FILE_MULTIBLOCK_READ_COUNTetc. to optimize database performance.
  • Use parallel processing: For large-scale data operations, parallel processing can be considered to improve performance and reduce competition for locks.

10. Regular maintenance

  • Regular analysis and optimization: Regularly analyze database performance, find bottlenecks and optimize them.
  • Clean up useless data: Regularly clean up data that is no longer needed, reducing the size of the table, thereby reducing competition for locks.

Summarize

Through the above steps, you can effectively solve the problem of locking tables in Oracle databases and find the SQL statements that cause locking tables. At the same time, by implementing best practices, the occurrence of table locking problems can be significantly reduced and the concurrency performance and stability of the system can be improved.

The above is the detailed content of the solution to Oracle lock tables and the best practices to avoid lock table problems. For more information on the solution and avoidance of Oracle lock tables, please pay attention to my other related articles!