[oracle實驗] Locking:資料一致性和完整性

公車上看concept，有關oracle鎖機制，跟MSSQL有些不同，抽空坐下實驗驗證一下

oracle通過鎖機制在事務間提供資料並發、一致性和完整性，這些操作自動執行，無需使用者幹預。
情景類比：多個使用者並發修改資料表的某一行。這裡實驗一個B/S應用，多使用者環境使用下列語句修改 HR.EMPLOYEES表

UPDATE employeesSET    email = ?, phone_number = ?WHERE  employee_id = ?AND    email = ?AND    phone_number = ?

這個語句確保在應用程式查詢並顯示給終端使用者之後，正在修改的employee_id資料不會被修改。這樣，應用程式避免出現一個使用者覆蓋了另一個使用者做出的修改的問題，或叫lost update 

跟著下表操作驗證：

時間	Session 1	Session 2	解釋
t0	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 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=118AND email='GHIMURO'AND phone_number='515.127.4565';1 row updated.		In session 1, the hr1 user updates the phone number in the row to 515.555.1234, which acquires a lock on the GHIMURO row.
t3		UPDATE hr.employees SET phone_number='515.555.1235' WHERE employee_id=118AND 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 to update the same row, but is blocked because hr1 is currently processing the row. The attempted update by hr2 occurs almost simultaneously with the hr1 update.
t4	COMMIT; Commit complete.		In session 1, the hr1 user commits the transaction. The commit makes the change for 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 a 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=118AND 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 the wrong phone number. The user starts a 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 the uncommitted change made at t6.
t8		UPDATE hr.employees SET phone_number='515.555.1235' WHERE employee_id=118AND 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 to update the same row, but is blocked because hr1 is currently processing the row.
t9	ROLLBACK; Rollback complete.		In session 1, the hr1 user rolls back the 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.

在 t2,t3,t4,t5時刻，很顯然session2對employee的修改無效，避免了lost update發生。