Shulou(Shulou.com)05/31 Report--

This article mainly introduces "deadlock analysis of Oracle". In daily operation, I believe many people have doubts about deadlock analysis of Oracle. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "deadlock analysis of Oracle"! Next, please follow the editor to study!

Background description of the problem:

Multiple processes that have deadlocks execute the same stored procedure, and the code and order are as follows:

-1. First, use the primary key order_no to lock an order select. * from order t where t. Orderkeeper noorder for update;-- 2. Second, use the primary key channel_id to lock a channel select. * from channel t where t.channelroomidlocked channel for update;-- 3. Then use the primary key order_no to modify the order table data update order t set t.orderstatusdata 0.finishcalendar timetables sysdate where t.orderlocks noisy tables orderlocks noisy situation description

Session A

-- statement 3 is being executed while he is in enq: TX-allocate ITL entry waiting for update order t set t.orderstatusstatustimedate where t.orderflowers timekeeping sysdate where t.ordercalendar notables

Session B

-- statement 2 is being executed and he is in enq: TX-row lock contention waiting for select t.* from channel t where t.channelroomidinitich2' for update.

Session C

-- statement 2 is being executed and he is in enq: TX-row lock contention waiting for select t.* from channel t where t.channelroomidinitich2' for update.

There may be more session executing statement 2 and waiting for enq: TX-row lock contention. For the time being, there are only 3 session listed here, but 2 is enough, but the probability is very low.

Waiting chain

An is blocked by C, C by B, and B by A.

Wait chain analysis:

An executes to statement 3, which means that the order data row lock with the primary key orderno_a and the channel data row lock for ch2 have been acquired, while the rest B and C can only wait for the row lock of the ch2 data to be released.

Both B and C have executed statement 2, indicating that they have acquired their respective order data row locks, and that the data is not the data represented by orderno_a. There is no doubt about that.

Question: the operations of A Magna B ·C are all different order data rows, and they all get their own row locks. Why is A blocked by C on the table order?

If you want to know why there is this doubt, you must first understand that A has already obtained the itl resource when executing the for update of order, so the waiting enq: TX-allocate ITL entry should not exist when the real update data is later, because he has already obtained the resource.

Deadlock analysis

To analyze this deadlock, you need to understand the meaning of the resource itl transaction slot represented by the wait event enq: TX-allocate ITL entry. An itl transaction slot is a record used to mark a transaction in a data block. The key point here is the data block. Think about what happens if the transaction crosses the data block. This is the key point of this deadlock. Of course, transactions in different tables must span data blocks, and even if a transaction modifies multiple pieces of data in a table, it is possible to cross blocks. In this case, transactions on the order table are operated through the primary key, which is the case for a piece of data that spans blocks, row migrations, or row joins.

Row migration usually occurs after update. For example, an err_mesg field initially has only 10 characters, followed by a update of 1000 characters. If the original data block cannot be loaded at this time, he will find another data block to store, while the dba (data block address) of a new data block on the original data block points to the new data block, as shown below:

A row join usually occurs when an insert occurs. For example, if a piece of data is so large that it cannot fit into a block, the oracle will be split into multiple blocks to store. You can test by creating tablespaces with small block sizes.

At this point, it is important to understand that itl is a resource on a data block. Even in the same transaction, if a transaction spans a data block, when he wants to modify the data block, he needs to apply for itl resources again on this new block, that is, in my deadlock, assuming that the block pointed to by the orderno_a data rowid is dba_1, the block pointed to in the row migration is dba_2, and at the beginning of the for update, the itl resource in the block dba_1 is obtained. When the real update data is at last, in order to protect the operation, you need to get the itl resources on the dba_2. At this time, many other session, such as BLI C. N session, have exhausted the itl resources on the block dba_2, so session An is in the state of waiting for the itl resources on the block dba_2, corresponding to enq: TX-allocate ITL entry. Other session will wait for session A to release the lock on the channel table data. The closed loop of the lock is completed

So far, deadlock analysis is complete.

You can use the following code to do a simple test-- create the order table, set PCTFREE to 0min initRANS and set it to 1create table t_order (mesg varchar2 (4000)) PCTFREE 0 INITRANS 1 to create the channel table create table t_channel (id NUMBER) -- prepare the data. For the order table, at least two blocks must have data. For the first block, there are three items, that is, a recorder brecedicine cinsert into t_order select rpad ('axiaoyongjinyua') from dual;insert into t_itl select rpad (' ballerina pencils 1000 pencils') from dual;insert into t_order select rpad ('cantilevered pencils 3000lc') from dual -- change data b. At this time, the first block cannot be loaded, and row migration will occur: update t_order set mesg= (select rpad ('baked pamphlet 3000 from dual) from dual) where mesg like' b%' -- you can use the following statement to analyze the table of row migration, which is only used as a test, and can be used carefully in online production. You can dump the first data block to find it. Which dba was migrated to create table CHAINED_ROWS (owner_name varchar2 (30), table_name varchar2 (30), cluster_name varchar2 (30), partition_name varchar2 (30), subpartition_name varchar2 (30), head_rowid rowid, analyze_timestamp date) Analyze table t_order list chained rows;select * from CHAINED_ROWS;-- continues to insert data to increase the migrated block data, making it convenient for for update to consume the itl resources of this block-usually, the data inserted below is the insert into t_order select rpad ('donglime1000) from dual;insert into t_order select rpad of the block after data migration.) from dual Insert into t_order select rpad ('for update; select from t_order where mesg like 300) from dual;insert into t_order select rpad (' session B select from t_order where mesg like') from dual;/* begins to simulate deadlock * /-T1 moment-session A select * from t_order where mesg like'b% 'for update; select * from t_channel where id=1 for update;-T2 moment-session B select * deadlock%' for update Select * from t_channel where id=1 for update;-- waits for session A to be released-the rest of session select * from t_order where mesg like'f% 'for update; select * from t_channel where id=1 for update;-- joins the row lock of this piece of data waiting for select * from t_order where mesg like'g%' for update; select * from t_channel where id=1 for update -- wait for the row lock that joins the data. / * if the data is not in the block where b is located, you can specify the migrated block where DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) = 'block_no' and DBMS_ROWID.ROWID_ROW_NUMBER (ROWID) = 1 after changing b by setting the where condition to the following -- at this time, session B and the rest of the session exhausted the itl of the second block of t_order, that is, the block where the data was located-- T3 moment-- session A to change the data of t_order, update t_order t set t.mesgAccording to bbbbbb 'where t.mesg like' b%'. -- at this time will be waiting for session B and other session to release itl resources, while session B and other session are waiting for session A to release channel locks-- forming a mutual waiting, closed loop, deadlock formation at this point, the study on "deadlock analysis of Oracle" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.