PL/SQL Lock

Concurrent Access: Multiple users access a database at the same time. (DBMS database management System) concurrency control: The correct way to achieve the concurrency of transactions, to avoid data instability, inconsistent. Problems with concurrent access:

1: Update lost: When one transaction modifies a row of data, another user modifies the line data at the same time, causing the first transaction to lose updates to the data.

2 . Dirty read: One transaction reads uncommitted data from another transaction.

　　3, non-repeatable read : When a transaction reads the data that was previously read, it finds that the data has been modified by another committed transaction.

　　4. Phantom Read : A transaction re-executes the query based on the same query condition, and the returned record contains rows that are different from the records returned by the previous execution of the query.

The basic concept of locks: a mechanism for sharing resources to control concurrent access

1: The lock is automatically managed by Oracle, and the duration of the lock is equal to the time of the transaction being committed. The user can also lock the resource through a statement.

2: The single-user database does not require a lock. Adding a lock can result in a performance drain.

Classification of Oracle Locks:

A: Shared lock (share locks) also known as read lock, S lock

Two: Exclusive lock (exclusive locks), also called write lock, X lock

Oracle Base Lock

First, lock

A database is a shared resource used by multiple users. When multiple users store data concurrently, the database generates multiple transactions that simultaneously access the same data. If the concurrency operation is not controlled, it is possible to read and store incorrect data and compromise the consistency of the database.

Lock is a very important technology to realize concurrency control of database. When a transaction makes a request to the system before it operates on a data object, it locks it. After locking, the transaction has some control over the data object, and other transactions cannot update the data object until the transaction releases the lock.

Second, blocking　　

Definition: 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 inserts, UPDATE, DELETE, SELECT ... For UPDATE

INSERT: The only case where insert is blocked is if the user has a table with a PRIMARY key constraint. When 2 sessions attempt to insert the same data into the table at the same time, one of the sessions is blocked until another session commits or rolls. When one session commits, another session receives the primary key duplicate error. The blocked session will continue to execute when the rollback is rolled back.

Update and delete when the data row that performs the update and delete operations has been locked by another session, blocking occurs until another session commits or rolls.

Select ... for update

When a user issues a select: When the for update operation prepares to make modifications to the returned result set, blocking occurs if the result set has been locked by another session. You need to wait until another session finishes before you can continue. You can avoid blocking by issuing a statement of SELECT ... for update nowait, and return the following error if the resource is already locked by another session: Ora-00054:resource busy and Acquire with nowait Specified.

Deadlock-deadlock

A deadlock occurs when two users want to hold the resources of the object, that is, when two users wait for each other to release the resource, Oracle considers it to produce a deadlock, in which case another user continues to execute at the expense of one user, and the sacrificed user transaction is rolled back.

For example:

(1) User 1 to the A table update operation, not submitted.

(2) User 2 to the B table update operation, not submitted.

There is no resource sharing issue at this time.

(3) If User 2 update the A table at this time, the block will occur, waiting for the end of User 1 's transaction.

(4) If User 1 now makes an update to table B, a deadlock will occur.

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.

At this point, Oracle chooses one of the users to roll back, allowing another user to continue the operation. Automatically discovers deadlocks in an Oracle system and chooses the least costly, which is to undo the transaction with the least amount of work done, releasing the resources owned by the firm and letting other transactions continue.

　　

You can avoid deadlocks by following these precautions:

1. For update and delete operations, only the rows to be changed should be locked and submitted immediately upon completion of the modification.

2. When multiple transactions are being updated with shared updates, do not use shared locks, but share update locks.

3, as far as possible to the operation of a table of concurrent transactions to apply a shared update lock, which can improve parallelism.

4. It is not appropriate to modify the underlying data structures (tables, indexes, clusters, and views) during periods of high application load.

III. Classification of Oracle Locks

　　1, according to the user and the System division.

(1) Auto Lock: When a database operation is performed, by default, the system automatically obtains all necessary locks for this database operation. Automatic locks are also classified as DML, DDL, Internal, and latch.

(2) Display Lock: In some cases, the data required for the locked database operation to be displayed by the user is needed to make the database operation perform better. The display lock is set by the user for the database object.

　　2, by the lock level division.

　　(1) Exclusive lock: When a data object is added to an exclusive lock, other transactions cannot read and modify it;

　　(2) shared lock: data Objects with shared locks can be read by other transactions, but cannot be modified.

　　3, according to the Operation division.　　

(1) DML Lock (data locks): Used to protect the integrity of data;
(2) DDL Lock (dictionary locks, Dictionary Lock): The structure used to protect database objects (such as tables, views, index structure definitions);
(3) Internal Locks and latches (internal lock and latch): Protect the internal database structure;
(4) Distributed locks (distributed lock): used in OPS (parallel server);
(5) PCM locks (parallel cache management Lock): Used in OPS (parallel server).

4, Pessimistic lock

Locks work before the user modifies them:
Select: For update (NOWAIT)
Select * from TAB1 for update
After the user issues this command, Oracle will establish a row-level blockade of the data in the returned set to prevent other users from modifying it.
If a DML or DDL operation is performed by another user at this time on the data returned by the result set above, an error message is returned or a blockage occurs.
1: The update or delete operation on the returned result set is blocked.
2: DDL operation on this table will be reported: Ora-00054:resource busy and acquire with nowait specified.

Cause Analysis:
At this point, Oracle has added row-level locks to the returned result set, and all other modifications or deletions to the data must wait for the lock to be released, and the external phenomenon is that other operations will be blocked, this operation commits or rollback.
The same transaction for this query will add a table-level lock to the table, do not allow any DDL operations on the table, or the ora-00054 error will be reported: Resource busy and acquire with nowait specified.

5. Optimistic lock

Optimistic that the data in the select out to update the enterprising and submit the time data will not be changed. There is a potential danger that the chosen result set is not locked and there is a possibility that another user might be able to change it. So Oracle still suggests a pessimistic blockade, because it's safer.

Four, DML lock

The most important lock in Oracle is a DML lock (also known as data locks, lock). The purpose of a DML lock is to ensure data integrity in the case of concurrency. In Oracle databases,DML locks mainly include TM and TX locks, where TM locks are called table-level locks, andTX locks are called transaction or row-level locks.

You can query the system lock condition using the following query statement.　　

SELECT * from V$locked_object;  SELECT * from V$lock;

　　1, TM Lock (table-level lock)

　　TM There are 5 types of lock (table level lock ) , called shared lock (s lock), exclusive lock (x Lock), row level shared lock (RS Lock), row level lock (RX Lock), shared row level exclusive lock (SRX Lock)

　　

Table 1 TM Lock types for Oracle
Lock mode	Lock description	Explain	SQL operations
0	None
1	Null	Empty	Select
2	SS (Row-s)	Row-level shared locks, other objects can only query these data rows	Select for UPDATE, lock for update, lock row share
3	SX (Row-x)	Row-level exclusive locks that do not allow DML operations before committing	Insert, Update, Delete, Lock Row share
4	S (Share)	Shared locks	Create Index, Lock share
5	SSX (S/row-x)	Shared row-level exclusive lock	Lock Share Row Exclusive
6	X (Exclusive)	Exclusive lock	Alter table, drop able, DROP index, Truncate table, Lock exclusive

When a transaction obtains a row lock, this transaction also automatically obtains the row's table lock (shared lock) to prevent other transactions from affecting the update of the record row through the DDL statement. A transaction can also acquire shared and exclusive locks during the process, and the transaction will obtain an exclusive lock on the table only if the transaction shows that an exclusive lock is defined using the Lock table statement, or you can use the lock table to display a shared lock that defines a tag.

Grammar:

LOCK TABLE <table_name> in <lock_mode> mode [NOWAIT];

The Lock_mode is a locked mode:

The schema for table-level locks includes the following content.

1) shared lock (Share Table lock,s):
Lock syntax: Lock Table TableName in Share Mode;
Allowed actions: A shared lock is controlled by one transaction, allowing only other transactions to query the table that is locked. A valid shared lock is explicitly used with select ... The For update form locks the row, or executes the lock table TableName in Share mode syntax to lock the entire table and does not allow updates by other transactions.
Forbidden Action: A shared lock is controlled by a transaction to prevent other transactions from updating the table or executing the following statement:
LOCK TABLE TableName in SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName in ROW EXCLUSIVE MODE;

2) Exclusive lock (Exclusive Table lock,x):
An exclusive lock is a type of lock that is most restrictive in the lock mechanism, allowing a transaction with its lock to control write access to the table on its own.
Lock syntax: Lock Table TableName in Exclusive Mode;
Allowed actions: Only one transaction in a table can have an exclusive lock on the table, and exclusive locks allow only other transactions to query the table.
Forbidden Operation: A transaction with an exclusive lock prohibits other transactions from executing statements of any other DML type or adding any other type of lock on the table.
Defines the syntax for an exclusive lock:
LOCK TABLE TableName in EXCLUSIVE MODE;

3) Row level lock (row Share Table lock,rs):
A row-level lock (sometimes referred to as Subshare table lock, or SS, child shared Lock) requires that the transaction be locked in update form on the table of the locked row. A row-level lock is automatically added to the action table when the following statement is executed.
SELECT ... From TableName ... For UPDATE of ...;
LOCK TABLE TableName in ROW SHARE MODE;
Row-level Locks (row Share table lock) are the least restrictive in the lock type, and are the most used in the concurrency of the table.
Allowed actions: Row-level shared locks are controlled by one transaction, allowing other transactions to query, insert, UPDATE, delete, or lock rows on the same table at the same time. So other transactions can get row-level locks on the same table at the same time, shared row-level exclusive locks, row-level exclusive locks, and exclusive locks.
Forbidden Operation: A transaction with row-level locks does not allow other transactions to perform an exclusive lock, that is:
Lock Table TableName in Exclusive Mode;

4.) row level exclusive lock (row Exclusive Table Lock,rx):
Row-level exclusive locks (also known as subexclusive table lock, referred to as SX, sub-exclusive locks) typically require a transaction-owned lock to be updated one or more rows on the table. When the following statement is executed, row-level row locks are added to the operating table.
INSERT into TableName ...;
UPDATE TableName ...;
DELETE from TableName ...;
LOCK TABLE TableName in ROW EXCLUSIVE MODE;
Row-level exclusive locks are slightly more restrictive than row-level locks.
Allowed actions: Row-level exclusive locks are owned by a transaction that allows other transactions to execute queries, modify, insert, delete, or lock rows on the same table at the same time. Transactions with row-level locks allow other transactions to have both shared and row-level locks on the same table.
Prohibited operations: Row-level exclusive locks are owned by a transaction that prevents other transactions from manually locking the table to exclude read and write access to other transactions. Therefore, other transactions do not allow the use of the following statement on the same table to perform a lock transaction.
LOCK table table in SHARE MODE;
LOCK table table in SHARE EXCLUSIVE MODE;
LOCK table table in EXCLUSIVE MODE

5) shared row-level exclusive lock (Share row Exclusive Table LOCK,SRX):
Shared row-level exclusive locks are sometimes called shared sub-exclusive locks (Share subexclusive Table lock,ssx), which have more restrictions than shared locks. The syntax for defining a shared row-level exclusive lock is:
Lock Table TableName in Share Row Exclusive Mode;
Allowed actions: Only one transaction is allowed to get row-level exclusive locks at a time. Owning a row-level exclusive lock transaction allows other transactions to execute a query on a locked table or use Select ... From TableName for update ... To accurately lock a row without updating the row.
Forbidden Operation: A transaction with row-level exclusive locks does not allow other transactions to have other forms of locks except shared locks on the same table or update the table. That is, the following statement is not allowed:
LOCK TABLE TableName in SHARE MODE;
LOCK TABLE TableName in SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName in ROW EXCLUSIVE MODE;
LOCK TABLE TableName in EXCLUSIVE MODE;

When a TX lock wait occurs on an Oracle database, failure to handle it often causes the Oracle database to hang or cause a deadlock to occur, resulting in ORA-60 errors.

　　2. TX Lock (Transaction lock or row level lock)

Row-level locks are generally used in Oracle databases.

When Oracle detects a deadlock, the execution of the deadlock-related statement is interrupted and rolled back, and the ORA-00060 error is reported and recorded in the log file AlertSID.log of the database. At the same time, a trace file is generated under User_dump_dest, which describes the deadlock information in detail.

In daily work, if you find that the ora-00060 error message is logged in the log file, a deadlock is generated. At this point, you need to find the corresponding trace file, based on the information of the tracking file to locate the cause.

Table 2 Data dictionary View description
View Name	Describe	Main Field Description
V$session	Information about the query session and the lock.	sid,serial#: Represents the session information. Program: Represents the application information for the session. Row_wait_obj#: Represents the awaited object, and corresponds to the object_id in Dba_objects.
V$session_wait	Query the session information that is waiting.	Sid: Represents the session information that holds the lock. Seconds_in_wait: Represents the time information waiting for the duration Event: Represents the events that the session waits for.
V$lock	Lists all the locks in the system.	Sid: Represents the session information that holds the lock. Type: Represents the kind of lock. Values include TM and TX, and so on. ID1: The object ID that represents the lock. Lmode,request: The letter that represents the lock mode of the session waiting Interest. indicated by the number 0-6, and corresponds to table 1.
Dba_locks	A formatted view of the V$lock.	SESSION_ID: Corresponds to SID in V$lock. Lock_type: Corresponds to the type in V$lock. LOCK_ID1: Corresponds to ID1 in V$lock. Mode_held,mode_requested: And V$lock The lmode,request corresponds to the corresponding.
V$locked_object	Contains only the lock information for DML, including rollback segments and session information.	XIDUSN,XIDSLOT,XIDSQN: Indicates rollback segment information. And V$transaction associated. OBJECT_ID: Represents the identity of the locked object. SESSION_ID: Represents the session information that holds the lock. Locked_mode: The letter that represents the lock mode of the session waiting V$lock and Lmode in the same.

Unlock and Kill Session:

Use the following syntax to isolate the lock and kill the session.

SELECT A.sid,a.serial#,a.username,b.type from v$session a,v$lock B WHERE a.sid=b.sid;

ALTER SYSTEM KILL SESSION ' sid,serial# ';

PL/SQL Lock