[Oracle experiment] locking: data consistency and integrity

Check the concept on the bus. The Oracle lock mechanism is somewhat different from MSSQL. Take the time to sit down and test it.

Oracle uses the lock mechanism to provide data concurrency, consistency, and integrity between transactions. These operations are automatically executed without user intervention.
Scenario Simulation: multiple users concurrently modify a row of a data table. Here we will experiment with a B/S application. In a multi-user environment, use the following statement to modify the HR. Employees table.

 
UpdateEmployeesSetEmail=?, Phone_number=?WhereEmployee_id=?AndEmail=?AndPhone_number=?
 

This statement ensures thatProgramAfter the data is queried and displayed to the end user, the data of the employee ID being modified is not modified. In this way, the application avoids the problem that one user overwrites the changes made by another user, orLost update

Follow the following table for verification:

Time	Session 1	Session 2	Explanation
T0	SelectEmployee_id, email, phone_numberFromHR. EmployeesWhereLast_name= 'Himuro'; Employee_id email phone_number------------------------------118Ghimuro515.127.4565		In session 1, The HR1 user queries HR. employees for the Himuro record And displays the employee_id (118 ), Email (ghimuro), and phone number (515.127.4565) attributes.
T1		select employee_id, email, phone_number from HR. employees where last_name = ' Himuro ' ; employee_id email phone_number -- --------- ------- ------------ 118 ghimuro 515.127 . 4565	In Session 2, The HR2 user queries HR. employees for the Himuro record And displays the employee_id (118 ), Email (ghimuro), and phone number (515.127.4565) attributes.
T2	Update HR. Employees Set Phone_number = ' 515.555.1234 ' Where Employee_id = 118 And Email = ' Ghimuro ' And Phone_number = ' 515.127.4565 ' ; 1 Row updated.		In session 1, The HR1 user updates Phone number in the row 515.555.1234, which acquires a lock on The ghimuro row.
T3		Update HR. Employees Set Phone_number = ' 515.555.1235 ' Where Employee_id = 118 And Email = ' Ghimuro ' And Phone_number = ' 515.127.4565 ' ; -- SQL * Plus does not show -- A row updated message or -- Return the prompt.	In Session 2, The HR2 user attempts Update the same row, but is blocked Because HR1 is currently processing Row. The attempted update by HR2 occurs Almost simultaneously with the HR1 Update.
T4	Commit; Commit complete.		In session 1, The HR1 user commits Transaction. The commit makes the change Himuro permanent and unblocks Session 2, which has been waiting.
T5		0 rows updated.	In Session 2, The HR2 user discovers that The ghimuro row was modified in such Way that it no longer matches its Predicate. Because the predicates do not match, Session 2 updates no records.
T6	Update HR. Employees Set Phone_number= ' 515.555.1235 ' Where Employee_id = 118 And Email = ' Ghimuro ' And Phone_number = ' 515.555.1234 ' ; 1 Row updated.		In session 1, The HR1 user realizes that it Updated the ghimuro row with Wrong phone number. The user starts New transaction and updates the phone Number in the row to 515.555.1235, Which locks the ghimuro row.
T7		select employee_id, email, phone_number from HR. employees where last_name = ' Himuro ' ; employee_id email phone_number -- --------- ------- ------------ 118 ghimuro 515.555 . 1234	In Session 2, The HR2 user queries HR. employees for the Himuro record. The record shows the phone number Update committed by session 1 at T4. Oracle Database read consistency Ensures that Session 2 does not see Uncommitted change made at T6.
T8		Update HR. Employees Set Phone_number = ' 515.555.1235 ' Where Employee_id = 118 And Email = ' Ghimuro ' And Phone_number = ' 515.555.1234 ' ; -- SQL * Plus does not show -- A row updated message or -- Return the prompt.	In Session 2, The HR2 user attempts Update the same row, but is blocked Because HR1 is currently processing Row.
T9	Rollback; Rollback complete.		In session 1, The HR1 user rolls back Transaction, which ends it.
T10		1 row updated.	In Session 2, the update of the phone Number succeeds because the Session 1 Update was rolled back. The ghimuro Row matches its predicate, so the update Succeeds.
T11		Commit; Commit complete.	Session 2 commits the update, ending The transaction.

At the T2, T3, T4, and T5 moments, it is clear that session2's modification to the employee is invalid, avoiding lost update.