公车上看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发生。