What is the cause of Oracle deadlock?

Reason: 1. The order of resource access between transactions alternates, and two users access the table locked by the other party, resulting in a deadlock; 2. Two users concurrently modify the same record, resulting in a deadlock; 3 , a statement that does not meet the conditions is executed, and improper indexing causes a deadlock in the global scan.

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

What is the cause of Oracle deadlock

The database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions will occur in the database at the same time. access to the same data. If concurrent operations are not controlled, incorrect data may be read and stored, destroying the consistency of the database. Locking is a very important technology to achieve database concurrency control. Lock-related exceptions are often encountered in practical applications. When two transactions require a set of conflicting locks and the transaction cannot continue, a deadlock will occur, seriously affecting the normal execution of the application.

Lock type:

1. Shared locks (Share Locks, S lock): Data objects with shared locks can be read by other transactions, but cannot Revise. The database uses these two basic lock types to control the concurrency of database transactions.

2. Exclusive Locks (X lock): When a data object is locked with an exclusive lock, other transactions cannot read or modify it.

3. Exclusive lock: Based on the shared lock, it is upgraded to an exclusive lock.

4. Update lock: All users can read, but I may do update operations in the future. I have obtained the qualification from shared lock (used for reading) to exclusive lock (used for updating). Only one update lock per transaction can be eligible for this.

Cause of occurrence:

1. Alternation of resource access sequence between transactions: a user 1 accesses table A (locks table A), and then accesses again Table B; another user 2 accesses table B (locks table B), and then attempts to access table A; at this time, user 1 has to wait for user 2 to release table B because user 2 has locked table B before continuing. The same user 2 must wait for user 1 to release table A before continuing, and a deadlock occurs. Solution: Program logic problem, pay attention to the calling sequence of the table

2. Concurrently modify the same record: User 1 queries a record, and then modifies the record; then user 2 modifies the record, then user 1 The nature of the lock in the transaction is attempted to be increased from the shared lock of the query to an exclusive lock, and the exclusive lock in user 2 must wait for 1 to release the shared lock because 1 has a shared lock, while 1 cannot be increased due to 2's exclusive lock. Exclusive locks make it impossible to release shared locks, so a deadlock occurs:

3. Improper indexing leads to full table scan: the transaction executes a statement that does not meet the conditions, executes a full table scan, or the amount of table data is very large When too few or inappropriate large indexes are created

Solution: Do not use overly complex queries that relate multiple tables in SQL statements and build indexes for optimization

Note:

1. oracle View deadlocks and release deadlocks

select sess.sid,     sess.serial#,     lo.oracle_username,     lo.os_user_name,     ao.object_name,     lo.locked_mode     from v$locked_object lo,     dba_objects ao,     v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid; 
alter system kill session '738,1429';  --释放资源

2. mysql View deadlocks and release deadlocks

show processlist         --查看数据库中各个进程的运行状态
select * from information_schema.innodb_trx        ---查询正在运行的事务
kill id

Recommended tutorial: "Oracle Video Tutorial"

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