Oracle Learning Note Seven lock

The concept of Locks

• A lock is a mechanism that a database uses to control concurrent access to shared resources.
• Locks are used to protect data that is being modified
• No other user can update the data until the transaction is committed or rolled back
• concurrency control of data to ensure consistency and integrity.

Parallelism-Allows multiple users to access the same data consistency-only one user is allowed to modify data integrity at a time-providing the correct data for all users. If a user is modified and saved, the modifications are reflected to the type row-level lock for all user locks: Locks the data rows of the operation and prevents other users (connections) from modifying them. Table-level Lock: locks the entire table. Row-level locks lock the row that is being modified.  Other users can access rows other than those that are locked. A row-level lock is an exclusive lock that prevents other transactions from modifying this row. Oracle automatically applies row-level locks when using the following statement: 1.INSERT 2.UPDATE 3.DELETE 4.SELECT ... For Updateselect ... The FOR UPDATE statement allows the user to lock multiple records at once for updates.locked data is self- rollback or can be unlocked by commit or rollbackSELECT ... For UPDATE syntax:

SELECT ... For UPDATE [of columns]
[WAIT N | NOWAIT];

 select  *   order_master where  vencode=    v002              for  update  of   Odate, del_date;  update  order_master set  Del_date=   "  '   commit ; 

SELECT *  from WHERE Vencode='V002'forUPDATE5;

SELECT *  from WHERE Vencode='V002'forUPDATE NOWAIT;

Table-level lock table-level locks lock the entire table, limiting other users ' access to the table.

Using a table-level lock to lock the table with a command, the syntax for applying a table-level lock is:

LOCK TABLE table_name in mode mode;

• Row sharing : Row share, which prohibits exclusive locks (exclusive locks are not allowed to be locked), allows other users to lock table data rows for shared use.

Table  in row share mode;

• Row Exclusive : Exclusive prohibit exclusive and shared locks

Table  in row exclusive mode;

• Shared lock : share, multiple users can lock a table at the same time, but only allow users to query and cannot modify the data

• • 1. Lock the table to allow other users to query the rows in the table only
  • 2. Prohibit other users from inserting, updating, and deleting rows
  • 3. Multiple users can apply this lock on the same table at the same time

• Shared row exclusive : Share row exclusive more restrictions than shared locks, prohibit use of shared locks and higher locks

• Exclusive lock : Exclusive, prevents other users from locking the strongest table lock, allowing other users to query the table's rows only. Prohibit modifying and locking tables

Deadlock when two transactions wait for each other to release resources, a deadlock is formed, and Oracle automatically detects the deadlock and resolves the deadlock by ending one of the transactions:

--First step: User 1UpdateEmp2SetSal=Sal+ - whereEmpno=7782;--Step Two: User 2UpdateEmp2SetSal=Sal+ - whereEmpno=7566;--Step Three: User 1UpdateEmp2SetSal=Sal+ - whereEmpno=7566;--Fourth Step: User 2UpdateEmp2SetSal=Sal+ - whereEmpno=7782;--Create a deadlock!! Oracle Auto Unlock! 

Oracle Learning Note Seven lock