[oracle实验] Locking:数据一致性和完整性
公车上看concept,有关oracle锁机制,跟MSSQL有些不同,抽空坐下实验验证一下
oracle通过锁机制在事务间提供数据并发、一致性和完整性,这些操作自动执行,无需用户干预。
情景模拟:多个用户并发修改数据表的某一行。这里实验一个B/S应用,多用户环境使用下列语句修改 HR.EMPLOYEES表
UPDATE employees SET 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 |
|
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 |
|
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 the |
|
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 to |
|
t4 |
COMMIT; |
In session 1, the hr1 user commits the |
|
t5 | 0 rows updated. | In session 2, the hr2 user discovers that
the GHIMURO row was modified in such a |
|
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 |
|
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 |
|
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 to |
|
t9 |
ROLLBACK; |
In session 1, the hr1 user rolls back the |
|
t10 | 1 row updated. |
In session 2, the update of the phone |
|
t11 |
COMMIT; |
Session 2 commits the update, ending |
在 t2,t3,t4,t5时刻,很显然session2对employee的修改无效,避免了lost update发生。