ORACLE 中丢失更新以及实例演示

水深无声 2022-01-28 08:15 360阅读 1赞

###  ###

*  一、丢失更新问题
 *  二、丢失更新实例演示

# 一、丢失更新问题 #

丢失更新 是一个经典的数据库问题  
出现下面情况时就会发生丢失更新  
1、会话1的一个事务获取（查询）了一行数据，放入本地内存，并显示给一个最终用户user1.  
2、会话2 中的另一事务也获取了这一行，并将数据显示给另一个最终用户user2  
3、user1使用应用修改了这一行数据，并让应用更新数据库并提交，会话1中的事物  
现在已经完成  
4、user2 也修改了这一行，并让应用更新数据库并提交，会话2中的事物，现在已经完成。

这个过程称为丢失更新，因为第三步所做的所有修改都会丢失， 举个常用的应用场景，例如一个用于简单更新员工界面，允许修改地址、工作电话号码等信息。简单地使用了select 和update命令，并没有考虑锁。  
然后用户user1在查询到一个员工信息后修改了地址信息，并点击了保存，得到了更新成功的确认信息。  
但是第二天user1再次查询该员工的信息是发现地址还是原先的地址，发生这个问题太容易了。  
当用user1第一次查询记录后，紧接着user2也查询了同一条记录。也就是在user1用读取数据之后，但在他修改数据之前，user2也读取了这条数据。然后user2查询数据之后，user1执行了更新，甚至user1还可能能再次查看确认自己修改后的信息。不过接下来user2更新了工作电话号码字段，并点击了保存。user2完全不知道他已经用旧数据覆盖了  
user1的更新的地址数据。  
还需要注意的是，要想发生这种情况，user1和user2甚至不用同时处理这条记录，只需要在大致相同的时间处理这个记录就会造成丢失更新。

# 二、丢失更新实例演示 #

有个员工表如下

select  t.* from  Emp_a t

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]  
step1 窗建修改员工数据存储过程

CREATE OR REPLACE PROCEDURE update_emp_a(p_empno INT) IS
        v_empno NUMBER;
        v_ename VARCHAR2(10);
        v_job   VARCHAR2(10);
    
    BEGIN
        SELECT t.empno, t.ename, t.job
          INTO v_empno, v_ename, v_job
          FROM emp_a t
         WHERE t.empno = p_empno;
        dbms_output.put_line('user 1查看数据 empno:' || v_empno || '  ename:' ||
                             v_ename || '  job:' || v_job); --模拟页面显示数据
        DBMS_LOCK.SLEEP(20); --让程序暂停20秒  模拟用户2在这期间查出数据
        UPDATE emp_a t
           SET t.empno = v_empno, t.ename = 'JAMES', t.job = v_job
         WHERE t.empno = p_empno; --修改员工姓名
        COMMIT; --模拟页面保存提交
        --模拟用户回看修改记录
        SELECT t.empno, t.ename, t.job
          INTO v_empno, v_ename, v_job
          FROM emp_a t
         WHERE t.empno = p_empno;
        dbms_output.put_line('user1 修改后数据 empno:' || v_empno || '  ename:' ||
                             v_ename || '  job:' || v_job);
    END;

step 2 再创建一个修改数据存储过程  
因为是通过查询进行修改，所以这里是不在一个事务里面，我们需要再创建一个修改存过

CREATE OR REPLACE PROCEDURE update_emp_a2(p_empno INT) IS
        v_empno NUMBER;
        v_ename VARCHAR2(10);
        v_job   VARCHAR2(10);
    
    BEGIN
        SELECT t.empno, t.ename, t.job
          INTO v_empno, v_ename, v_job
          FROM emp_a t
         WHERE t.empno = p_empno;
        dbms_output.put_line('user2 查看数据 empno:' || v_empno || '  ename:' || v_ename ||
                             '  job:' || v_job); --模拟页面显示数据
         DBMS_LOCK.SLEEP(20); --让程序暂停20秒  用户1在这期间修改数据并回看数据
         UPDATE emp_a t
           SET t.empno = v_empno, t.ename = v_ename, t.job = 'SALESMAN'
         WHERE t.empno = p_empno; --修改员工姓名
        COMMIT; --模拟页面保存提交
      --模拟用户回看修改记录
        SELECT t.empno, t.ename, t.job
          INTO v_empno, v_ename, v_job
          FROM emp_a t
         WHERE t.empno = p_empno;
        dbms_output.put_line('user1 修改后数据 empno:' || v_empno || '  ename:' ||
                             v_ename || '  job:' || v_job);
    END;

step 3 修改员工编号是7566的数据  
现在员编号7566的名字是CURREY，job是manager。  
本例实现用户1 修改名字为JAMES ，用户2修改job为salesman

通过sqlplus两个窗口登录数据，模拟两个用户登录。  
然后先在登录第一个窗口执行存过

set serveroutput on
     exec update_emp_a(7566);

![在这里插入图片描述][2019052317160386.png]

从执行结果中看到user1 已经修改了员工名字为JAMES，并且还回看了。  
然后过几秒在另外一个窗口执行 模拟用户2操作场景

set serveroutput on
     exec update_emp_a2(7566);

![在这里插入图片描述][20190523171627437.png]

同时对于user2 同样修改了员工的职位为SALESMAN。还需要注意的是此时user1用户修改员工的姓名已经被覆盖了。  
这种情况常常出现在应用程序里，我们通过select把数据查出来后  
把查出的数据赋值给一个新建的实体类，然后先对实体类修改后再把数据保存到数据库中。  
如果出现两个以上的用户大致时间类进行修改时，就会导致数据更新丢失。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]: /images/20220128/734a62957d1c4023bff60a4f4d1a9887.png
[2019052317160386.png]: /images/20220128/8d345bc5dc62456483e0879a46f2d66e.png
[20190523171627437.png]: /images/20220128/44bc260635d24ef4b3cbe3e54b3817ac.png