Detailed graphic and text explanation of Oracle lock table solutions

This article brings you relevant knowledge about Oracle. When developing Oracle database, we often encounter Oracle data tables that are frequently operated, and Oracle lock tables will appear. Here is an introduction to you I have found relevant information about Oracle lock table solutions. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

I believe everyone is familiar with table locks or lock timeouts, which often occur in DML statement, the reason is the exclusive locking mechanism of the database. When a DML statement is executed, the table or row data is locked until the transaction is committed or rolled back or the current session is forcibly ended.

For our application system, table locking will most likely occur where SQL execution is slow and there is no timeout (a SQL has been unsuccessfully executed for some reason (the Spoon tool does data extraction and push) and has been Do not release resources) Therefore, it is particularly important to write efficient SQL! There are also other situations where table locking may occur, which is a high concurrency scenario. The problem caused by high concurrency is that Spring transactions will cause database transactions to be uncommitted and cause deadlock (the current transaction is waiting for other transactions to release lock resources)! Thus throwing the exception java.sql.SQLException: Lock wait timeout exceeded;.

So how to solve the lock table or lock timeout? The temporary solution is to find out the table or statement that is competing for the lock resource, directly end the current session or session, and force the lock resource to be released. For example

The solution is as follows:

1. Session1 modifies a certain piece of data but does not submit the transaction, and session2 queries the record of the uncommitted transaction

2. Session2 tries to modify

We can see that the record of modifying uncommitted transactions will be in a waiting state until the other party releases the lock resource or forcibly closes session1. This also shows that Oracle has achieved row-level locks!

This is just a simple simulation of the table lock situation. You can see at a glance that it is the table lock caused by session1. When encountering this situation in actual development, SQL is generally used to directly find out the tables or statements that compete for lock resources and then forcefully release the resources! !

3. Session3 queries the tables or statements that compete for resources and forces the resources to be released.

-- 查询未提交事务的session信息，注意执行以下SQL，用户需要有DBA权限才行
SELECT
    L.SESSION_ID,
    S.SERIAL#,
    L.LOCKED_MODE AS 锁模式,
    L.ORACLE_USERNAME AS 所有者,
    L.OS_USER_NAME AS 登录系统用户名,
    S.MACHINE AS 系统名,
    S.TERMINAL AS 终端用户名,
    O.OBJECT_NAME AS 被锁表对象名,
    S.LOGON_TIME AS 登录数据库时间
FROM V$LOCKED_OBJECT L
    INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
    INNER JOIN V$SESSION S ON S.SID = L.SESSION_ID
WHERE 1 = 1

The query results are as follows

forces the release of resources for us. Only the first two fields are useful. For example,

-- 强制 结束/kill 锁表会话语法
ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';

-- 强制杀死session1，让session2可以修改id=5的那条记录
ALTER SYSTEM KILL SESSION '34, 111';

After forcibly killing session1, pay attention to the execution of session2! We will find that the waiting of session2 will be terminated and executed immediately! I believe everyone has a doubt, session_id is 29 and 34, how to determine whether they belong to session1 or session2, and ensure that session1 is killed so that session2 can successfully execute the DML statement?

In fact, it is also very simple. The judgment here The method is that session1 performs the update but does not submit the transaction. You can first use the above SQL to query the session information of the uncommitted transaction. At this time, the information found is the information of session1.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Detailed graphic and text explanation of Oracle lock table solutions. For more information, please follow other related articles on the PHP Chinese website!