Reason analysis and solution for Oracle Packet lock

http://blog.csdn.net/jojo52013145/article/details/7470812

During the development of a database, it is common to encounter problems in which the packages, stored procedures, functions cannot be compiled or compiled, and PL/SQL cannot respond. Encounter this problem, basically have to restart the database to solve, a serious waste of development time. This article will make a basic introduction to the causes and solutions of this phenomenon.

• Problem analysis

In the database development are aware of the concept of locks, such as: the implementation of the Update Table xxx Where xxx will generate a lock. This common lock is called a DML lock in Oracle. There is also a DDL lock in Oracle that is primarily used to guarantee the integrity of database objects such as stored procedures, table structures, views, packages, and so on, which can be found in dba_ddl_locks. Note: The V$locked_object record is the DML lock information, and the DDL lock information is not inside.

The DDL is the DDL statement, and the DDL statement is the full data definition statement (Define Language). The structure or schema used to define the data, such as CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME. When we execute a stored procedure, or compile it, Oracle automatically adds a DDL lock to the object and locks the object referenced by the stored procedure.

Having learned the above knowledge, we can conclude that the compiler package has no response for a long time and the deadlock is generated. We can easily make this deadlock happen, for example:

1. Open a PL/SQL, start debugging a function (assuming: fun_core_servicecall), and keep it in the debug state

2. Open a SQL Window, enter select *from dba_ddl_locks awhere a.name = ' Fun_core_servicecall ' to find a row of records:

3. Open a new PL/SQL and recompile the function. We're going to find that we're not responding now.

4, back to the first PL/SQL, re-execute select *from dba_ddl_locks awhere a.name = ' Fun_core_servicecall ' We will see the following records:

5. The above-mentioned situation indicates that a lock wait has occurred.

In Oracle, DDL locks are divided into categories: Exclusive DDL Locks (exclusive DDL), Share DDL Locks (Shared DDL Lock), breakable parse Locks (can be broken by parsing locks). The space is limited, here is no longer detailed introduction. According to this example, when we try to compile, modify stored procedures, functions, packages, etc. to the data object, if others are compiling or modifying them will create a lock wait, or when we compile a stored procedure, if it refers to the database object is being modified should also produce a lock wait. This hypothetical sibling of interest can be tested, but more difficult.

• Solution Solutions

Encounter this problem, if you know who is locked up (can find out), you can let the other side as soon as possible to release the lock, it can not be found only by hand to kill the lock. The steps are as follows:

1, first find out which processes lock this object, the statement is as follows:

Select b.sid,b.serial#

Fromdba_ddl_locks A, v$session b

Where a.session_id= B.sid

and a.name = ' fun_core_servicecall ';

2. Execute the following statement kill process: Alter system kill session ' sid,serial# '

3, after the execution of the above statement, sometimes it is not certain to be able to kill the process. This time you need to connect to the database server to kill the server-side process, query statements:

Select spid, Osuser, S.program

Fromv$session s, v$process p

Where s.paddr= p.addr

and S.sid = (SID found above)

4. Execute the following statement on the server:

#kill-9 spid (Unix platform)

Orakill SID Thread (Windows platform SID is the instance name of Oracle, thread is the SID found above)

5, after the execution of 4 steps can basically kill the process of the deadlock, do not rest assured that you can perform the first step of confirmation.

This article can only be said to do a preliminary analysis of this problem, the cause of this problem and the solution involves a lot of basic knowledge of Oracle. such as: DML statement, DDL statement, mechanism of Oracle lock, relationship between V$session and v$process, etc. Interested brothers can be more in-depth study, welcome to communicate with me!

Deadlocks are frequently a problem with databases, and databases generally do not generate deadlocks for no reason, and deadlocks are usually caused by the design of our application itself. How to solve the deadlock, here is the general solution:

1) Execute the following SQL to see which tables are 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;

2) Investigate the session that caused the deadlock
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;
The SID is listed here

3) Identify SIDs and serial#:
Check v$session view:
SELECT sid,serial#,paddr from v$session WHERE sid= ' SID ' just found;
This step will get paddr

4) Check v$process view:
SELECT SPID from v$process WHERE addr= ' just found paddr ';
This step gets the SPID

5) Kill the process
(1) In the database, kill the Oracle process:
ALTER SYSTEM KILL SESSION ' isolated sid, isolated serial# ';



The following error occurs when executing a stored procedure that updates data in a table:


sql> exec Update_jc_kxx_yxrq;

Begin UPDATE_JC_KXX_YXRQ; End

ora-20998:err=-2049,msg=0-ora-02049: timeout: Distributed transaction wait lock
ORA-06512: In the Icuser. Update_jc_kxx_yxrq ", line 36
ORA-06512: On line 2





Query deadlock with SYS user login database

Sql> Select Username,lockwait,status,machine,program from v$session where SID in
(select session_id from V$locked_object);

USERNAME lockwait STATUS
------------------------------ ---------------- --------
Machine
----------------------------------------------------------------
Program
------------------------------------------------
Icdb
JDBC Thin Client

Icuser 000000038a37c0c8 ACTIVE
Icdb
JDBC Thin Client


Indicates that the database has a deadlock





Then use the statement to find the dead-locked statement

Sql> Select Sql_text from V$sql where Hash_value in
(select Sql_hash_value from v$session where SID in
(select session_id from V$locked_object));

Sql_text
--------------------------------------------------------------------------------
Update Jc_kxx SET ljxf =NVL (ljxf, 0) +: 1, YE =:2 WHERE KH =:3
Update jc_kxx set zt= ' where kh= ' 1000530330 '


Then use the following statement to find the process that was deadlocked

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

USERNAME object_id session_id serial#
------------------------------ ---------- ---------- ----------
Oracle_username Os_user_name PROCESS
------------------------------ ------------------------------ ------------
Icuser 30523 32 42463
Icuser Oracle

Icuser 30523 28 25508
Icuser Oracle

Icuser 30523 76 14781
Icuser Oracle




USERNAME object_id session_id serial#
------------------------------ ---------- ---------- ----------
Oracle_username Os_user_name PROCESS
------------------------------ ------------------------------ ------------
Icuser 30523 24 37522
Icuser Oracle


Use the statement to kill the deadlock process

Alter system kill session ' sid,serial# '; (where sid=l.session_id)

such as:sql> alter system kill session ' 24,37522 ';


Execute the stored procedure again, the error is gone. Statement execution succeeded! 

reason analysis and solution for Oracle Packet lock

Reason analysis and solution for Oracle Packet lock