Types of locks, blocking, deadlock generation and solutions.

Types of TM Locks:

TM locks are mutually exclusive relationships of several modes:

Blocking

Defined:
Blocking occurs when one session keeps a lock on the resource that another session is requesting. The blocked session is suspended until the session holding the lock discards the locked resource. 4 common DML statements can cause blocking
INSERT
UPDATE
DELETE
SELECT ... For UPDATE

——————————————————————————————————————————————————————————————

Blocking test for UPDATE:

1. Get the session SID

Sql> select Sid from V$mystat where Rownum=1; (Session SID for update first)

Sid
----------
1

Sql> select Sid from V$mystat where Rownum=1; (Session SID for subsequent update)

Sid
----------
39

2. Operation table

Sql> select * from Andy; (View action table)

Id
----------
1

Sql> update Andy set id=2 where id=1; (First, the session SID is 1 update)

1 row updated.

Sql> update Andy set id=2 where id=1; (Subsequently, session SID is 39 update, after execution, discovery is blocked)

3. Viewing session lock Conflicts

Sql> Select Sid,type,lmode,id1,id2,request,block from V$lock where type in (' TM ', ' TX ') Order by 1, 2;

SID TY lmode ID1 ID2 REQUEST BLOCK
---------- --  ---------- ---------- ---------- ---------- ----------
1 TM 3 79729 0 0 0
1 TX 6 131072 1276 0 1
3 79729 0 0 0
0 TX 131072 1276 6 0

Explanatory notes:

Sid: Represents the session SID.

TY: Represents the type of lock.

ID1: The identity of the modified table (OBJECT_ID) is represented in the TM line. Example: 1 TX 6 131072 1276 0 1

ID1: Represents a decimal value in the TX line that represents the number of the rollback segment that the transaction occupies and the slot number (slot numbers, which can be understood as a record number) in the Transaction table (Transaction table) of the rollback segment. The form is: 0xRRRRSS: 1 TX 6 131072 1276 0 1

Request:0 indicates that a lock has been acquired. A non-0 means that the lock is blocked and the request is received. For example, a session with a SID of 1, first update Andy set id=2 where id=1; get the TX lock for the row, but commit. Then the SID is 39 for the session, again Andy set id=2 where id=1, the TX lock of the line is requested, but the line TX lock is occupied by a session with a SID of 1, so not getting that said, is blocked and can only wait (Sid 1 commits with itself rollback).

Block: Indicates whether the lock is blocking other locks. The number represents a blocking of several

--Verify that the identity of the modified table is represented in the TM line (OBJECT_ID). (This is true)

Sql> Select object_name from dba_objects where object_id=79729;

object_name
--------------------------------------------------------------------------------
ANDY

At this point, the blocking test is complete.

Relationship of lock waits between query sessions

Select a.sid holdsid,b.sid waitsid,a.type , A.id1,a.id2,a.ctime from V$lock A,v$lock b
where a.id1=b.id1 and A.id2=b.id2 and a.block = 1 and B.block = 0;

Holdsid waitsid TY ID1 ID2 CTIME
---------- ---------- -- ---------- ---------- ----------
1, TX 458777 1066 503
1, TX 458777 1066 503

Check for blocked sessions
SELECT * from V$lock where lmode=0 and type in (' TM ', ' TX ');

——————————————————————————————————————————————————————————

Deadlock-deadlock

Definition: A deadlock occurs when two users want to hold each other's resources.
That is, when two users wait for each other to release resources, Oracle determines that a deadlock is generated, in which case another user continues to execute at the expense of one user, and the transaction of the sacrificed user is rolled back.
Example:
1: User 1 update to a table, no commit.
2: User 2 Update to B table, no commit.
At this point, there is no resource sharing problem with double-anti.
3: If user 2 at this time to update a table, then the block will occur, you need to wait until the end of the user one thing.
4: If user 1 then update the B table, a deadlock is generated. At this point, Oracle chooses one of the users to roll over, allowing another user to continue the operation.
Cause:
Oracle's deadlock problem is actually very rare, and if it happens, it's basically an incorrect design, and after the adjustment, it basically avoids the deadlock.

Deadlock test:

Sql> select * from Andy;

Id
----------
3
2

sql> delete Andy where id=2; (Session 1)

1 row deleted.

sql> delete Andy where id=3; (Session 2)

1 row deleted.

sql> delete Andy where id=3; (Session 1)
Delete Andy where id=3
*
ERROR at line 1:
Ora-00060:deadlock detected while waiting for resource

sql> delete Andy where id=2; (Session 2, blocking state)

After the deadlock is generated, the method of processing:

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) View the session that caused the deadlock
>select B.username,b.sid,b.serial#,logon_time
18:09:40 2 from V$locked_object a,v$session b
18:09:40 3 WHERE a.session_id = B.sid order by B.logon_time;

3) View blocked sessions
>select * from Dba_waiters;

4) You can either commit or roll back the block, release the lock or kill the Oracle process:
ALTER SYSTEM KILL SESSION ' sid,serial# ';
Description
If all deadlock-related sessions are killed, the transaction is rolled back for all sessions, which is equivalent to the transaction being not executed.
If you kill a session that reports Ora-00060:deadlock detected while waiting for resource, blocking the session commit, the blocking session will complete all the transactions for that session.
If you kill a blocked session and report a ORA-00060 session commit, the completed transaction is the one before the birth or death lock.
If the ora-00060:deadlock detected while waiting for resource session is rollback, blocking the session commit, the blocking session will complete all transactions for that session.

OK, end. Reprint please indicate the source.

Types of locks, blocking, deadlock generation and solutions.