When does Oracle lock a table?

Oracle lock table situations include: 1. Program A performs an insert on tableA, but before commit, program B also performs an insert on tableA. At this time, a resource busy exception will occur, which is a table lock. ; 2. Table locking often occurs in concurrency rather than parallelism. In parallel, when one thread operates the database, the other thread cannot operate the database. CPU and I/O allocation principles.

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

The main reasons for locking tables are:

1. Program A performs an insert on tableA, but before commit, program B also performs an insert on tableA. At this time, an exception that the resource is busy will occur, which is the lock table

2. The lock table often occurs in concurrency rather than parallelism (in parallel, when one thread operates the database, the other thread cannot operate the database, cpu and i/o allocation principle)

Methods to reduce the probability of locking the table:

1. Reduce the time between the execution of insert, update, and delete statements and commit. 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 to submit , will also cause table locking.

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';

Among them, 1025 is sid and 41 is serial#.

2. The reason why Oracle users are often locked

prompts when logging in: test user is locked

1. Use the dba role Log in as a user to 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.

The above is the detailed content of When does Oracle lock a table?. For more information, please follow other related articles on the PHP Chinese website!