Shulou(Shulou.com)06/01 Report--

one。 Deadlock between deletion and update

The cause of deadlock is the contention or interdependence of multiple threads or processes for the same resource. Here is an example of a deadlock caused by a scramble for the same resource.

CREATE TABLE testLock (ID NUMBER

Test VARCHAR (100)

COMMIT

INSERT INTO testLock VALUES (1, 'test1')

INSERT INTO testLock VALUES (2, 'test2')

COMMIT

SELECT * FROM testLock

ID TEST

1 test1

2 test2

The recurrence of deadlock:

1. Execute in the sql window: SELECT * FROM testLock FOR UPDATE;-add row-level locks and modify the content, do not submit

Query deadlock:

Select s.usernamevideo l.objectroomid, l.sessionlockedwatches, s.serialrecords, s.lockwaitparents.statusparents.machineparamagens.program from v$session spapering lockedweights object l where s.sid = l.session_id

Field description:

Username: the database user used by the deadlock statement

SID: session identifier, session identifier, session is a context between the two parties from the beginning of the communication to the end of the communication.

SERIAL#: sid will be reused, but when the same sid is reused, serial# will increase and will not be repeated.

Lockwait: you can use this field to query information about the lock you are currently waiting for.

Status: used to determine the status of the session. Active: executing SQL statement. Inactive: wait for the operation. Killed: marked as deleted.

Machine: the machine on which the deadlock statement is located.

Program: which application is the main source of the deadlock statement.

At this time, there is a line of commands waiting for the operation Inactive

View the statement that caused the deadlock:

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))

There is no deadlock statement at this time.

two。 Open another command window and execute: delete from testLock WHERE ID=1

Deadlock occurs at this time (note that another window should be opened at this time, otherwise it will prompt: POST THE CHANGE RECORD TO THE DATABASE. Force commit after clicking yes):

Deadlock View:

SQL > select s.usernamejurisdiction l.objectroomid, l.sessionlockedpresentation s.serialkeeper, s.lockwaitparents.statusparents.machineparamers.program from v$session smallvested lockedrecording object l where s.sid = l.session_id

View the statement that caused the deadlock:

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))

Find the following statement deadlock:

Delete from testLock where ID = 1

Handling of deadlocks: alter system kill session 'session_id,serial#'

Alter system kill session '301meme 16405'

If you look at the deadlock again, you will find that there is no record that stauts is active, and the statement in which the deadlock occurred has been terminated.

two。 Deadlock caused by no indexing on a foreign key

ORA-60 deadlocks frequently occur in the customer's 10.2.0.4 RAC for AIX environment, causing the application to fail to execute smoothly.

After a series of diagnosis, it is found that the final problem is that there is no index on the foreign key, because the program deletes data on the master and child table, and the lack of index leads to the upgrading of row-level locks to table-level locks, resulting in a large number of lock waiting and deadlocks.

Here is a simple simulation of the problem through an example:

SQL > create table tweep (id number primary key, name varchar2 (30))

Table created.

SQL > create table tweef (fid number, f_name varchar2 (30), foreign key (fid) references tweep)

Table created.

SQL > insert into tweep values (1,'a')

1 row created.

SQL > insert into tactif values (1,'a')

1 row created.

SQL > insert into tweep values (2,'b')

1 row created.

SQL > insert into tactif values (2,'c')

1 row created.

SQL > commit

Commit complete.

SQL > delete tactif where fid = 2

1 row deleted.

At this point, the child table is also deleted in session 2:

SQL2 > delete tactif where fid = 1

1 row deleted.

Go back to session 1 to perform the deletion of the primary table:

SQL > delete tweep where id = 2

The session is locked and go back to session 2 to perform the deletion of the master table:

SQL2 > delete tweep where id = 1

The session is also locked, and the statement of session 1 is rolled back with an ORA-60 deadlock error:

Delete tweep where id = 2

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

SQL > rollback

Rollback complete.

The session 1 operation rolls back, and session 2 also rolls back and indexes on the foreign key column:

1 row deleted.

SQL2 > rollback

Rollback complete.

SQL2 > create index ind_t_f_fid on tweef (fid)

Index created.

Repeat the above step session 1 to delete the child table record:

SQL > delete tactif where fid = 2

1 row deleted.

Session 2 delete child table records:

SQL2 > delete tactif where fid = 1

1 row deleted.

Session 1 deletes the master table record:

SQL > delete tweep where id = 2

1 row deleted.

Session 2 Delete the master table record:

SQL > delete tweep where id = 1

1 row deleted.

All deletions can be performed successfully, so there is no in-depth analysis of the differences in lock information between the two cases. The focus is on indexing the foreign key column.

Although some articles have mentioned that it is possible not to build an index on a foreign key column if certain circumstances are satisfied, my view has always been that since a foreign key has been created, don't care about one more index, because the added cost of an index is negligible compared to the problems caused by missing this index.

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.