Will insert in Oracle lock the table?

Insert in oracle will lock the table; when Oracle executes DML statements such as insert, it will apply for a lock on the table being operated. When two insert statements attempt to operate on a table at the same time, a session will occur. It is blocked, waiting for other sessions to commit or rollback, thus causing a deadlock.

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

Will insert in oracle lock the table?

Will insert in oracle lock the table?

When ORACLE executes DML statements such as insert, it will automatically first Apply for a TM lock on the table to be operated. When the TM lock is obtained, it will automatically apply for a TX type lock. When two or more sessions execute DML language on the same record of the table, the first session locks the record, and the other sessions are in a waiting state until the TX lock is released after the first session commits, and the other sessions Only sessions can be locked.

Consideration is because two insert statements try to insert data with the same PK or unique value into a table at the same time, causing the session to be blocked, waiting for other sessions to commit or rollback, thus causing a deadlock.

In this case, as long as any one of the sessions is submitted, the other one will report ORA-00001: unique constraint is violated and the deadlock terminates; or one of the sessions is rolled back and the other one can execute normally. .

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 main reasons for locking tables are:

(1) Program A performs an insert on tableA, and before commit, program B also performs an insert on tableA. Then a resource busy exception will occur at this time, which is lock table

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

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 Submitting will also cause the table to be locked.

Expand knowledge:

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;

Recommended tutorial: "Oracle Video Tutorial"

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