Problems caused by an Oracle row-Level Lock

In plsqldeveloper, I use the system user to connect to the database. In crt, I use sqlplusassysdba to connect to the database. It is a sys user. Now in plsqldeve

In plsql/developer, I use the system user to connect to the database. In crt, I use sqlplus/as sysdba to connect to the database. It is a sys user. Now in plsql/deve

Found the problem: Today, the development colleagues asked to modify several fields of a row of data, which is very simple, because the Oracle lock mechanism is not properly understood.

I connected to the database using both plsql/developer and CRT tools, and then planned to update a field on plsql/developer ,, update a field on the CRT (I don't know what I thought at the time, haha), and then I got stuck inexplicably ..... Due to lack of experience and knowledge, I was still waiting for it. I thought it was because the data volume was too large. It turned out to be the reason for the lock,

Analysis Problem: In plsql/developer, I use the system user to connect to the database, while in crt, I use sqlplus/as sysdba to connect to the database, which is a sys user. Now a field is updated in plsql/developer without commit, and another field is directly updated on the CRT, resulting in the latter waiting. The Row-Level Lock should already exist,
Solution:
1. Users with dba permission can view the locks of the database.

Select a. username, a. sid, a. serial #, a. logon_time
From v $ locked_object B, v $ session
Where B. session_id = a. sid order by a. logon_time;

2. view the specific SQL statement based on the sid. If the SQL statement is not important, kill

Select SQL _text from v $ session a, v $ sqltext_with_newlines B
Where DECODE (a. SQL _hash_value, 0, prev_hash_value, SQL _hash_value) = B. hash_value
And a. sid = & sid order by piece;
3. kill the transaction
Alter system kill session '00000045 ';
Then execute the update statement again. It is recommended that the update statement be executed by the same user very quickly.

This article permanently updates the link address: