The basic principle of lock

oracle032The basic principle of lock
1. Oracle Lock type
The role of the lock: to protect the data, no locks, no concurrency, locks are used to limit concurrency
Slow database response is not necessarily the data negative, or the cause of the lock block the transaction: Open two client modification data will obviously find that the second one is slow because the first user locked the row record

Latch Lock: Chain, Chain (server Pro acquires latch lock for protection chain when concurrent access)
Lock Lock: Buffer, data block, data row
Exclusive lock (x): is a access lock X and B cannot access the lock
Shared Lock (s): either A or b can be combined with the same share S lock
2. Row-level Lock: DML statement; is the minimum granularity lock lock; Oracle-specific also affects the efficiency of concurrency most
Transaction Lock TX: A transaction lock that is caused by a row-level lock
Structure of the Lock
Lock and unlock process for transaction locks: DML statements (locking); Commit/rollback (Unlocked)
Only exclusive locks (row-level and transaction-lock types are exclusive)
does not affect read (CR block)
3. Table level Lock: TM

Row-level exclusive lock (row exclusive) Rx lock
When we do DML, the RX lock is automatically added to the table being updated, and you can execute the lock command to explicitly add the RX lock on the table.
Allow other transactions to modify other data rows in the same table through DML statements
Allow the use of the lock command to add a RX lock to a table
Do not allow other transactions to add an X lock to a table
Row-level shared lock (row shared, referred to as RS lock)
Select ... the from for update generates a transaction that produces a row-level lock, like a normal DML statement, and adds an RS lock without a RX lock at the table level, but does not actually modify the data
Shared Lock (Share, abbreviation S lock)
Add the S lock via the lock table in share mode command
Exclusive Lock (Exclusive, abbreviation x Lock): Changes the structure of the table, and deletes the table; no other user can do anything
Add x lock via lock table in exclusive mode command
Shared row-level exclusive lock (Share row Exclusive, abbreviated as SRX Lock)
Add SRX lock with lock table in share row exclusive mode command

Lock table in [row Share][row exclusive][share][share row exclusive][exclusive] mode;

Research lock focuses on two points: lock compatibility, cause of lock generation
4, the compatibility of the lock
5. Lock statement and release of lock
6. Lock related View
V$transaction
XIDUSN indicates the number of the rollback segment used by the current transaction
Xidslot describes the record number of the transaction in the transaction table in the header of the rollback segment (also called the slot number)
XIDSQN Description Serial Number
Status indicates whether the transaction is an active
V$lock
Records the lock that the session has acquired and the lock information being requested
SID indicates the ID number of the session
Type description lock lock level, focus on TX and TM
Lmode indicates that a locked pattern has been obtained, expressed as a digitally encoded
Request describes the mode of the lock being requested, expressed as a digital encoding
Block indicates whether other users have been blocked from obtaining a lock, greater than 0 description yes, equals 0 description No

Lock mode lock abbreviation coded value
Row Exclusive RX 3
Row Shared RS 2
Share S 4
Exclusive X 6
Share Row Exclusive SRX 5
NULL N/A 0 or 1

V$enqueue_lock
The fields that are contained in the view and the fields mean exactly the same as the fields in V$lock.
Only the session information that was requested to be locked but not locked is displayed in the view.
The records are arranged according to the order of the time of the application lockout, the session that first applies for the lock is in front, the previous session will be locked first.

V$locked_object
The information for the object that is currently locked is logged
XIDUSN indicates the number of the rollback segment used by the current transaction
Xidslot the record number corresponding to the transaction table in the header of the rollback segment
XIDSQN Description Serial Number
OBJECT_ID describes the ID number of the currently locked object, which can be used to find the locked object name in dba_objects based on the ID number.
Locked_mode describes the digital encoding of the lock mode

V$session
Records information about the current session
Sid indicates the number of the session
serial# = serial Number
SIDs and serial# can be thought of as v$session primary keys, which collectively uniquely identify a session test lock:

Open 3 Session: Sql> select Sid from V$mystat where Rownum=1; Sid ---------- 29 Sql> Sql> select Sid from V$mystat where Rownum=1; Sid ---------- 35 Sql> Sql> select Sid from V$mystat where Rownum=1; Sid ---------- 38 Sql> Updating the T-table operation with SessionID 29 Sql> Update T set name= ' QQ ' where id = 2; 1 row updated. Sql> Query transactions: Select Xidusn,xidslot,xidsqn,status from V$transaction; 5 1099 ACTIVE Indicates that there is currently an active transaction The following is the transaction information generated by querying a connection of SessionID to 29: Select Sid,type,id1,id2, Decode (lmode,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Lock_mode, Decode (request,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Request_mode,block From V$lock where sid=29; SID, TYPE, ID1, ID2, Lock_mode, Request_mode, BLOCK AE 0 Share None 0 TM745850 Row Exclusive None 0//table-level lock TX 327710 1099 Exclusive None 0//row-level lock The SID indicates that Sessionid,type represents the type of lock, ID2 indicates the number of times the transaction slot is overwritten, and the information in ID1 contains two kinds of information: 1. Transaction table 2. Rows in a transaction table Pass: Disassemble the ID1 Select Trunc (327710/power (2,16)) as Undo_blk#,bitand (327710,to_number (' ffff ', ' xxxx ') + 0 as slot# from dual; undo_blk#, slot#5 30//shows the information of the transaction, which also shows that ID1 represents two kinds of information. Lock_mode represents the mode of the lock, request_mode whether the lock is requested, block indicates which transaction is lockedSelect Owner,object_name,object_id,object_type,created,last_ddl_time,status,namespace from Dba_objects where OBJECT _id=74585;//Locked Object OWNER, object_name, object_id, Object_type, CREATED, Last_ddl_time, STATUS, NAMESPACE SYS t 74585 t ABLE 2 February-December-14 April-January -15 VALID 1 for TM locks, ID1 indicates that objects that are locked are ID,ID2 always 0 For a TX lock, ID1 represents the rollback segment number used by the transaction and the corresponding record number in the Transaction table, ID2 indicates the number of times the record number has been reused (wrap) Update t table operation using SessionID for 35:sql> update T set name= ' WW ' where id = 2; No execution results are locked. The following is the transaction information generated by querying SessionID for connections of 29, 35: Select Sid,type,id1,id2, Decode (lmode,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Lock_mode, Decode (request,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Request_mode,block From V$lock where SID in (29,35) Order by Sid; SID, TYPE, ID1, ID2, Lock_mode, Request_mode, BLOCK29 TM 74585 0 Row Exclusive None 0 AE 0 Share None 0 TX 327710 1099 Exclusive None 1//lock a transaction TM 74585 0 Row Exclusive None 0 AE 0 Share None 0 29 TX 327710 1099 None Exclusive 0//request an exclusive lock, because the top one is locked in a lock. Update t table operation using SessionID for 38:sql> update T set name= ' WW ' where id = 2; No execution results, no indication is locked. Query the V$enqueue_lock to get the session information in the locked queue: Select Sid,type, Decode (request,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Request_mode From V$enqueue_lock where SID in (35,38); SID, TYPE, request_mode35 AE None + AE None TX Exclusive Exclusive TXyou know that the SessionID for 35,38 need to be locked for x lock. Query the relationships between several locks:Select A.sid Blocker_sid,a.serial#,a.username as Blocker_username,b.type, Decode (b.lmode,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Lock_mode, B.ctime as time_held,c.sid as Waiter_sid, Decode (c.request,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ' ) Request_mode, C.ctime time_waited From V$lock B, V$enqueue_lock C, v$session a where A.sid = B.sid and b.id1= c.id1 (+) and B.id2 = C.id2 (+) and c.type (+) = ' tx ' and b.type = ' tx ' and B.block = 1 Order by Time_held, time_waited; Blocker_sid, serial#, Blocker_username, TYPE, Lock_mode, Time_held, Waiter_sid, Request_mode, time_waited 29 55 7 SYS TX Exclusive 2452 Exclusive 274 557 SYS TX Exclusive 2452 Exclusive 712blocker_sid: SessionID of the lock for 29,blo Cker_username: The user name of the lock is sys,type: The type of lock is TX (row-level lock) Lock_mode: The model of the lock is an exclusive lock, Time_held: The duration of the lock is waiter_sid: Which SessionID lock waits for the lock, Request_mode: The type of request lock, time_waited: Request time through the above information can know the lock time held by a connection, if the time is too long to indicate that there is a problem with the connection, you can kill the connection alter system kill session ' 29,557';//The first parameter Blocker_sid, the second is the serial# parameter One transaction modifies multiple rows, resulting in a TX lock Select Sid from V$mystat where Rownum=1; Sql> Update T set name= ' RR ' where id=2; 1 row updated. Sql> Update T set name= ' RR ' where id=2; 1 row updated. Sql> Update T set name= ' RR ' where id=2; 1 row updated. Sql> Select Sid,type,id1,id2, Decode (lmode,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Lock_mode, Decode (request,0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ') Request_mode,block From V$lock where sid=38; SID, TYPE, ID1, ID2, Lock_mode, Request_mode, BLOCK38 AE 0 Share None 0 0 TM 74585 0 Row Exclusive None 327680 TX 1099 Exclusive None 0 The total number of TX locks that can be obtained is determined by the initialization parameter transactions, and the number of TM locks that can be obtained is determined by the initialization parameter dml_locks: Select Name,value from V$parameter where name in (' Transactions ', ' dml_locks '); NAME, Valuedml_locks 1084 Transactions 271 Querying for Resource usage: Select Resource_name as "R_n", current_utilization as "C_u", max_utilization as "M_u", initial_allocation as "I_u" From V$resource_limit where resource_name in (' Transactions ', ' dml_locks '); R_n, C_u, M_u, I_u dml_locks 1 1 1084 Transactions 1 1 271 The above parameters can be known that the Dml_locks resource is currently using 1 and most of the time only used 1, the largest resource is 1084transactions ibid, through this can determine the resource settings is If the value of M_u equals or is close to I_u, it is necessary to increase the I_u value because the total number of TX locks that can be obtained is determined by the initialization parameter transactions, and the number of TM locks that can be obtained is determined by the initialization parameters Dml_locks

Grant Select on V_$mystat to HR;

Dead lock
Two sessions (denoted by A and c), a deadlock occurs when a holds the lock that C is applying for, and C also holds a pending lock. Deadlock is a typical "double-lose" situation, if allowed to develop, there will be a and C these two sessions are executing transactions can not end the phenomenon. Therefore, in an Oracle database, the DML statement that caused the deadlock is revoked. Deadlocks are always caused by unreasonable application design.
When a session's transaction causes a deadlock, Oracle automatically revokes the corresponding DML statement in other transactions that are blocking the transaction, while other DML statements in other transactions that block the transaction are not revoked.

Session 1
Select Sid from V$mystat where Rownum=1;
Update Employees Set last_name=last_name| | ' A
where employee_id=100

Session 2
Select Sid from V$mystat where Rownum=1;
Update Employees Set last_name=last_name| | ' b
where employee_id=101;

Session 1
Update Employees Set last_name=last_name| | ' C ' where employee_id=101;

Session 2
Update Employees Set last_name=last_name| | ' d ' where employee_id=100;

To unlock and password settings for HR users: Sql> Select Username,password,account_status from dba_users where username= ' HR '; USERNAME PASSWORD ------------------------------ ------------------------------ Account_status -------------------------------- HR EXPIRED & locked//expired and locked status sql> alter user HR account unlock; User altered. Sql> Select Username,password,account_status from dba_users where username= ' HR '; USERNAME PASSWORD ------------------------------ ------------------------------ Account_status -------------------------------- HR expired//at this point HR just expires and is not locked sql> alter user HR identified HR; Alter USER HR identified HR * ERROR at line 1: ora-00924:missing by keyword sql> alter user HR identified by HR; User altered. Sql> Select Username,password,account_status from dba_users where username= ' HR '; USERNAME PASSWORD ------------------------------ ------------------------------ Account_status -------------------------------- HR open//Open State Sql>

The basic principle of lock