Deadlock and Oracle Deadlock--REPRINT

Today to see the group in the discussion of the database deadlock problem, also studied the next, check out some of the information summarized here.

The so-called deadlock: Refers to two or more than two processes in the course of execution, because of the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to proceed. At this point the system is in a deadlock state or the system generates a deadlock, and these processes, which are always waiting on each other, are called deadlock processes. Because the resource consumption is mutually exclusive, when a process requests resources, so that the process without external assistance, never allocated the necessary resources and can not continue to run, which creates a special phenomenon of deadlock.How to check database deadlockOne, the phenomenon of database deadlock program in the course of execution, click OK or save button, the program does not respond, there is no error.   Second, the principle of deadlock when the database of a table of a column do update or delete operations, after the completion of the statement is not committed, and another for this column of data to do update operation of the statement at the time of execution will be in the waiting state, the phenomenon is that this statement has been executing, but has not executed successfully, There are no errors.   The method of locating the deadlock by checking the database table, you can check out which statement is deadlocked, which machine is the one that generated the deadlock.  1) Executes the following statement with the DBA user select Username,lockwait,status,machine,program from v$session where SID in (select session_id from V$locked_object) If there is a result of the output, then there is a deadlock, and the machine that can see the deadlock is which one. Field Description: Username: The database user used by the deadlock statement; Lockwait: The state of the deadlock if there is a content representation being deadlocked. Status: State, active represents the machine where the deadlock statement is located. Program: The main application that generated the deadlock statement. 2) You can view the deadlock statement by executing the following statement with the DBA user. Select Sql_text from V$sql where Hash_value in  (select Sql_hash_value from v$session where SID in (select session_id f Rom v$locked_object)   Deadlock resolution method in general, as long as the statement that produces a deadlock can be committed, but in the actual execution of the process. The user may not know which sentence is the statement that generated the deadlock. You can shut down the program and restart it. Often encountered this problem in the use of Oracle, so also summed up a little solution. 1) Find the process of deadlock: Sqlplus "/as sysdba" (sys/change_on_install) Select S.username,l.object_id,l.session_id,s.serial#,l. Oracle_username,l.os_user_name,l.process from v$locked_object l,v$session S WHERE L.SESSION_ID=S.SID; 2) Kill this deadlock process: Alter system kill session ' sid,serial# '; (where sid=l.session_id)  3) If not resolved: Select Pro.spid from V$session ses,v$process Pro where ses.sid=xx and ses.paddr= pro.addr;  where SID is replaced with a deadlock SID: Exitps-ef|grep spid  where the SPID is the process number of this process, kill the Oracle process select A.sql_text, B. USERNAME, c.object_id, c.session_id, b.serial#, c.oracle_username,c.os_user_name,c.process, "' | | c.session_id| | ', ' | | b.serial#| | "  from v$sql A, v$session B, v$locked_object c where a.hash_value = b.sql_hash_value And B.SID = C.Session_I d 

Oracle locks are specifically divided into the following categories:

1. According to the user and the system, can be divided into automatic lock and display lock

Auto Lock: When a database operation is performed, by default, the system automatically obtains all necessary locks for this database operation.

Display Lock: In some cases, the data required for a locked database operation to be displayed by the user is needed to make the database operation perform better, and the display lock is set by the user for the database.

2. By lock level, can be divided into shared lock and exclusive lock

Shared locks: Shared locks allow one transaction to share access to a particular database resource-another transaction can access the resource or obtain the same shared lock. Shared locks provide high concurrency for transactions, but such as poor transaction design + shared locks can easily cause deadlocks or data updates to be lost.

Exclusive lock: After the transaction sets an exclusive lock, the transaction obtains this resource separately, and the other transaction cannot obtain a shared or exclusive lock on the same object before the transaction commits.

3. By operation, can be divided into DML lock, DDL lock

+DML lock can also be divided into, row lock, table lock, deadlock

-row Lock: When a transaction performs a database insert, UPDATE, delete operation, the transaction automatically obtains an exclusive lock on the action row in the action table.

-Table-level lock: When a transaction obtains a row lock, this transaction also automatically obtains the row's table lock (shared lock) to prevent other transactions from making DDL statements that affect the update of the record row. A transaction can also acquire a shared or exclusive lock during the process, and the transaction will get an exclusive lock on the table only if the transaction shows the definition of an exclusive lock displayed using the Lock table statement, or you can use the

The lock table shows the definition of a shared lock at the table level (please refer to the relevant documentation for the specific use of the lock table).

-Deadlock: A deadlock occurs when two transactions require a set of conflicting locks and cannot continue the transaction.

such as transaction 1 in table a row record # # There is a row of it lock, and waits for transaction 2 to record the release of the exclusive lock in table A, while transaction 2 has a row lock in table a record line # # #, and waits for transaction 1 to record the release of the exclusive lock in Table A, transaction 1 and Transaction 2 wait for each other, resulting in a deadlock. Deadlocks are generally caused by poor business design.

The following SQL is used to query which people or processes are locked:

SELECT object_id,session_id,serial#, A.oracle_username,a.os_user_name,a.process

From V$locked_object A,

V$session WHERE A.session_id=sid;

Unlock can be used under SQL: Alter system kill session ' sid,serial# ';

Or use the commands of the associated operating system kill process, such as the Kill-9 Sid under Unix, or use other tools to kill the deadlock process.

+DDL lock can also be divided into: Exclusive DDL lock, shared DDL lock, Analysis lock

-Exclusive DDL Lock: A DDL statement that creates, modifies, and deletes a database object obtains an exclusive lock on the action object.

When using the ALTER TABLE statement, the maintenance data is complete, consistent,

Legitimacy, the transaction obtains a row of its DDL locks.

-Shared DDL Locks: DDL statements that need to establish interdependencies between database pairs typically need to be shared

Gets the DDL lock.

If you create a package, the procedures and functions in the package refer to different database tables.

When this package is compiled, the transaction obtains a shared DDL lock on the referencing table.

-Analysis Lock: Oracle uses shared pool storage to analyze and optimize SQL statements and PL

Applications that run the same statement are faster. An object that is cached in the shared pool gets

The analysis lock that it references to the database object. Analysis Lock is a unique type of DDL lock,

Oracle uses it to track dependencies between the shared pool object and the database it references. When a transaction modifies or deletes a shared pool that holds an analysis lock on the database, the image

, Oracle invalidates the objects in the shared pool, and the next time you reference this SQL/PLSQL statement, Oracle parses the statement again.

4. Internal 閂 Lock

Internal 閂 Lock: This is a special lock in Oracle that accesses the internal system structure sequentially.

When a transaction needs to write information to the buffer, in order to use this block of memory, Oracle must first obtain the 閂 lock of the memory area to write information to this block of memory.

Original address and reference article http://xinxiangsui2018.blog.163.com/blog/static/106097856201010304532280/http://www.cnblogs.com/ highriver/archive/2011/04/06/2006684.html In addition Baidu Encyclopedia introduced also more detailed http://baike.baidu.com/view/121723.htm