Http://book.csdn.net/bookfiles/82/index.html6.2.6 deadlock If you have two sessions and each session holds the resources that the other session wants Deadlock ( Deadlock ). For example, if my database has two tables A And B Each table has only one row, so it is easy to show what is a deadlock. All I have to do is open two sessions (for example, two SQL * Plus Session ). In session A Updating table A And in the session B Updating table B . Now, if I want B Updating table A . Session A This row has been locked. This is not a deadlock; it's just blocking. I have not encountered a deadlock because of the session A There is a chance to submit or roll back the session. B To continue. If I go back to session A, I try to update the table. B This will lead to a deadlock. You need to select one of the two sessions as the "victim" to roll back its statements. For example, in session B A Updates may be rolled back and the following error is returned: To update a table B Of Session A is still blocked, and Oracle will not roll back the entire transaction. Only a deadlock-related statement is rolled back. Session B still locks the table B And session A is waiting patiently for this row to be available. After receiving the deadlock message, session B must decide B The unexecuted jobs are submitted or rolled back, or another way is continued, and then submitted later. Once this session is committed or rolled back, the other blocked session will continue, as if nothing had happened. Oracle considers deadlocks rare, and because they are rare, it creates a trace file on the server each time a deadlock occurs. The content of this tracking file is as follows: Apparently, Oracle considers the deadlock of these applications as an error caused by the application itself, and in most cases, this is true for Oracle. Unlike many other RDBMS, there are very few deadlocks in Oracle, and it can be considered almost nonexistent. Generally, a deadlock occurs only when conditions are provided manually. According to my experience, the number one cause of the deadlock is that the foreign key is not indexed (the second cause is that the bitmap index on the table is updated concurrently, which will be discussed in chapter 11th ). In the following two cases, oracle adds a full table lock to the child table after modifying the parent table: Q If the primary key of the parent table is updated (if the relational database principle is followed, that is, the primary key should be unchangeable, this is rare), because there is no index on the foreign key, therefore, the sub-table is locked. Q If a row in the parent table is deleted, the entire sub-table will also be locked (because there is no index on the foreign key ). In oracle9
I In and above versions, these full table locks are short-lived, meaning they exist only during DML operations, rather than during the entire transaction. Even so, these full table locks may (and will indeed) cause serious locking problems. The second point is described below. [2] If you use the following command to create two tables: Then execute the following statement: so far, there is no problem. However, if you attempt to delete the first parent record in another session, you will find that the session is blocked immediately. Before deleting the table, it tries C Add a full table lock. Currently, no other session can C Any row in Delete , Insert Or Update (The started session can be continued. [3] But the new session cannot be modified. C ). This blocking also occurs when the primary key value is updated. In relational databases, updating a primary key is a big taboo, so there is generally no problem in this regard. In my opinion, if developers use tools that can generate SQL, and these tools update each column, whether or not the end user actually modifies those columns, updating the primary key becomes a serious problem. For example, suppose we use Oracle Forms and create a default layout for the table. By default, Oracle Forms generates an update to modify each column in the table we select to display. If Dept Create a default layout in the table, which includes three fields. Dept Table Any Column, Oracle Forms will execute the following command: in this case, if EMP Table has Dept A foreign key in EMP Table Deptno If the column does not have any index, update Dept Overall EMP Tables are locked. If you use a tool that can generate SQL, be careful with this. Even if the primary key value is not changed, after the preceding SQL statement is executed EMP Will also be locked. If Oracle Forms is used, the solution is Update changed columns only Set property Yes . In this way, Oracle Forms will generate Update Statement, which only contains modified columns (excluding primary keys ). Deleting a row in the parent table may cause the sub-table to be locked, resulting in more problems. As I have already said, if you delete a table P In the DML operation, the sub-table C Will be locked, so as to avoid C Execute other updates (of course, there is a premise that no one is modifying C If someone is modifying C , Deletion will wait ). In this case, congestion and deadlock will occur. By locking the entire table C , Database concurrency will be greatly reduced, so that no one can modify C Any content in. In addition, the possibility of deadlock increases, because my session now "owns" a large amount of data and will not be handed over until it is submitted. Other sessions because C Blocking is more likely; as long as the session tries to modify C It will be blocked. As a result, I began to notice that a large number of sessions in the database were blocked, and these sessions held some other resource locks. In fact, if any blocked session locks the resources required by my session, a deadlock will occur. In this case, the cause of the deadlock is that my session does not allow others to access more resources than they need (in this case, all rows in a table ). If someone complains that there is a deadlock in the database, I will ask them to run a script to check whether there is a foreign key without an index. In the case of 99%, we will find that this problem exists in the table. Only foreign keys and indexes are needed, and deadlocks (and a large number of other competition problems) will disappear. The following example shows how to use this script to locate a table. C Foreign keys not indexed: This script will handle foreign key constraints, with a maximum of 8 columns allowed (If your foreign key has more columns, you may have to reconsider your design ). First, it creates a name named Cons Inline view ). This inline view transposes the appropriate column names in the constraints from rows to columns. The result is that each constraint has a row and a maximum of eight columns. These columns are the column names in the constraints respectively. In addition, this view also contains a column Col_cnt Contains the number of columns of the foreign key constraint. For each row returned in this inline view, we need to execute a correlated subquery to check all indexes on the currently processed table. It counts the number of columns matching the foreign key constraint in the index and groups the columns by the index name. In this way, you can generate a set of numbers, each of which is the total number of matching columns in an index of the table. If the original Col_cnt Greater All If these numbers are used, the table does not support the index of this constraint. If Col_cnt If the number is smaller than all of these values, at least one index supports this constraint. Note: Nvl2 Function. We use this function to "stick" the column name list to a comma-separated list. This function has three parameters: A, B, and C. If parameter A is not null, return B; otherwise, return the parameter C. This query has a premise that the owner of the constraint is also the owner of the table and index. If another user adds an index to a table, or the table is in another mode (both of which are rare), it cannot work correctly. Therefore, this script shows the table C In Column X There is a foreign key, but no index. Through X Add an index to completely eliminate this locking problem. In addition to the full table lock, foreign keys that are not indexed may also cause problems in the following situations: Q If On Delete Cascade , No index is added to the sub-table. : For example, EMP Yes Dept Child table, Delete deptno = 10 Should Cascade (Cascade) EMP [4] . If EMP In Deptno If no index exists, delete it. Dept When each row in the table is EMP Perform a full table scan. This full table scan may be unnecessary. If multiple rows are deleted from the parent table, the child table will be scanned every time a row is deleted from the parent table. Q Query a sub-table from the parent table: Think again EMP/Dept Example. Exploitation Deptno Query EMP Tables are quite common. If you frequently run the following queries (for example, generate a report), you will find that no index slows down the query: N Select * From Dept, EMP N Where EMP. deptno = Dept. deptno and dept. deptno =: X; So when do we not need to add external keys and indexes? The answer is: In general, you do not need to add an index when the following conditions are met: Q No Deletes a row from the parent table. Q No Update the unique key/primary key value of the parent table (note that the tool occasionally inadvertently updates the primary key !). Q No Join a child table from the parent table (for example Dept Connect EMP ). If all of the above conditions are met 3 You can skip the index without adding an index to the foreign key. If the above conditions are met, you must be careful with the consequences of adding an index. This is a rare case, that is Oracle "Excessively locked" data.
