What is the reason why Oracle locks the table?

In Oracle, the reason for locking the table is that when a program performs an insert on a table and has not yet committed, another program also performs an insert on the same table, then a resource will occur at this time. Busy exception, that is, lock table.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the reason why Oracle locks the table?

The main reasons for locking the table are:

(1) When program A executes the insert of tableA and has not yet committed, Program B also inserts tableA. At this time, a resource busy exception will occur, which is lock table

(2) Lock table often occurs concurrently rather than in parallel (in parallel, when one thread operates the database, another Threads cannot operate the database, CPU and I/O allocation principle)

Methods to reduce the probability of locking the table:

(1) Reduce the time between insert, update, and delete statement execution to commit time. Specifically, change batch execution to single execution and optimize the non-execution speed of sql itself

(2) Roll back things if there is an exception

The reason for locking the table may be that the row data has been modified and forgotten Submitting will also cause the table to be locked.

1. It is recommended to check the reason before making a decision when locking the table.

1 The code for lock table query has the following form:

select count(*) from v$locked_object;
select * from v$locked_object;

2. Check which table is locked

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

3 Check which session caused it

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

4. Check which sql is causing it

select b.username,b.sid,b.serial#,c.* from v$locked_object a,v$session b,v$sql c where a.session_id = b.sid
and b.SQL_ID = c.sql_id and c.sql_id = ''
order by b.logon_time;

5. Kill the corresponding process

Execute the command: alter system kill session'1025,41';

1025 among them is sid, 41 is serial#.

2. The reason why Oracle users are often locked

prompts when logging in: user test is locked

1. Users with the dba role Log in and unlock, first set the specific time format to view the specific time

SQL> alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;

Session altered.

2. View the specific locked time

SQL> select username,lock_date from dba_users where username='TEST';
USERNAME LOCK_DATE
------------------------------ -------------------
TEST 2021-03-10 08:51:03

3. Unlock

SQL> alter user test account unlock;

User altered.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is the reason why Oracle locks the table?. For more information, please follow other related articles on the PHP Chinese website!