【Oracle学习06】DML与并发性,UNDO,死锁

关系数据库中的数据由SQL的数据操作语言(Data Manipulation Language ,DML)命令进行行管理。DML包括INSERT，UPDATE，DELETE，MERGE。本节也会讨论和DML紧密相关的事务控制语句COMMIT和ROLLBACK。

说明：示例数据来自oracle自带的hr数据库。文中内容主要摘录于OCP考试指南，侵删。

6.1 数据操作语言(DML)语句

严格说有5种DML(Data Manipulation Language)命令:

• SELECT

• INSERT

• UPDATE

• DELETE

• MERGE

6.1.1 INSERT

语法:

insert into table values (value[,value...]);

示例:

create table emp_copy as select * from employees where 1=2;
insert into emp_copy select * from employees;
desc emp_copy;
select * from user_tab_columns where table_name='EMP_COPY';
##insert 可以条语句将多行插入多个表中。
insert all when department_id<80 then
 into emp_a(xxx) values (employee_id,department_id,salary)
when department_id>=80 then
into emp_b(xxx) values(employee_id,department_id,hire_date)
select department_id,department_id,salary,hire_date
from employess ;

说明：insert 可以用同时插入多个表中，如示例。

6.1.2 UPDATE

UPDATE 中SET子句中使用更新列的子查询必须为标量值。即只能是某数据类型的单值(不能是多个值)，否则会失败。

SQL> select salary*1.2 from employees where employee_id=206;
SALARY*1.2
----------
      9960
#如下update成功
update emp_copy set salary = (select salary*1.2 from employees where employee_id=206);

#如下将失败
update emp_copy set salary = (select salary*1.2 from employees where last_name like 'A%');
错误报告 -
ORA-01427: 单行子查询返回多个行

#如下slary将为null,因为没有last_name= 'A%'
select salary*1.2 from employees where last_name = 'A%'; 
update emp_copy set salary = (select salary*1.2 from employees where last_name = 'A%');

6.1.3 DELETE

#若无合适的记录，即0条记录删除，也是成功。
delete from emp_copy where last_name = 'A%';
0 行已删除。

6.1.4 TRUNCATE

• TRUNCATE不是DML命令，而是DDL命令。

• Delete 等DML影响数据时，它们插入，更新，删除是事务的一部分。而DDL是做为事务来执行，无法回滚。

• 和DML相比，DDL执行速度更快。Truncate是瞬间完成,会重置高水位线(high water mark)。

• 若是表中有活动的DML，则DDL会失败。事务会中断DDL命令，直到用COMMIT或者RoallBack终止DML为止。

#
TRUNCATE table emp_copy;
错误报告 -
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
#kill session后，可以
TRUNCATE table emp_copy;
select * from emp_copy;

说明：错误现象：TRUNCATE可能出错，ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效。原因：存在没commit提交DML，也没rollback回滚。处理：https://blog.csdn.net/czh500/article/details/89169447

select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id;
#session_id = 57,55
SELECT sid, serial#, username, oSUSEr,terminal,program ,action, prev_exec_start FROM v$session where sid in (57,55);
alter system kill session '57';

6.1.5 MERGE

Merge命令可以实现若是没有匹配行，就insert，若是存在就更新此行。

MERGE INTO table_name alias1
USING (table | view | sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
  UPDATE table_name SET col1 = col_val1, col2 = col2_val
WHEN NOT MATCHED THEN
  INSERT (column_list) VALUES (column_values);

操作alias1表的数据，其操作数据的来源为 alias2中数据的记录数，通过on括号中的条件进行过滤，当alias1和alias2中的数据通过on条件进行过滤后，如果存在就执行更新操作，如果不存在就执行插入操作。

6.1.6 DML语句失败

DML失败原因

• 语法错误

• 引用不存在的对象或列

• 访问权限

• 约束违背

• 空间问题

约束违背会导致DML语句失败。例如Insert 多条记录，其中有一条记录包括了重复键，将返回一个语句失败，此失败将触发撤消所有全部已成功的插入!。

6.2 控制事务

事务: 由一条名多个DML语句组件，后面紧跟ROLLBACK 或COMMIT命令，可以在事务内部使用SAVEPOINT命令给出控制程度。

6.2.1 数据库事务

Oracle确保事务完整性的机制是撤消段和重做日志文件的组件。

事务特性：

• 原子性 (Atomicity) ： 事务包含的所有操作要么全部成功，要么都不完成,回滚。

• 一致性(Consistency)： 事务开始前和结束后，数据库的完整性约束没有被破坏。查询的结果必须和查询开始的状态一致。Oracle用撤消段来保证。

• 隔离性(Isolation): 一个用户并发访问数据库时,不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。未完成提交的事务，是不可见的。

• 持久性(Durability): 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

Oracle用撤消段来保证事务的隔离性，一致性和原子性。

6.2.2 执行SQL语句

事务的开始和结束:

事务的开始和结束会话发出DML命令时,就开始事务。事务持续执行任何数量的DML命令,COMMIT或者ROLLBACK语句为止。

只有提交的变更才会变得永久,才会对事务不能嵌套,SQL标准不允许用户启动一个事务,然后在终止第一个事务之个事务。使用 PL/SQL( Oracle专有的第三代语言)可以这样做,但它不是行业标显式事务控制语句有 COMMIT、 ROLLBACK和 SAVEPOINT。

除了用户发出的COMMIT或者 ROLLBACK之外,还有一些情况会隐式终止事务:

• 发出DDL(CREATE,ALERT,DROP)或者DCL(GRANT/REVOKE)语句

• 退出用户工具(SQL*Pus、 SQL Developer或者其他工具)

• 客户会话终止

• 系统崩溃

6.2.3 事务控制: COMMIT, ROLLBACK,SAVEPOINT,SELECT FOR UPDATE

https://www.cnblogs.com/toughhou/p/3778802.html

1. COMMIT

COMMIT发生的操作是LGWR进程将日志缓冲区的内容刷新到磁盘。DBWn进程完全没有执行任何操作。任何DDL,DCL都将提交当前的事务。

2.ROLLBACK命令

ROLLBACK [TO SAVEPOINT savepoint];

3.SAVEPOINT命令

只有事务内才可以看到SAVEPOINT位置。语法:

SAVEPOINT identifier
ROLLBACK [WORK] TO SAVEPOINT identifier
RELEASE SAVEPOINT identifier

示例:

CREATE TABLE TMP003(ID NUMBER,"DESC" VARCHAR2(200));
INSERT INTO TMP003 VALUES(1,'第一条记录');
#设置保存点bk
SAVEPOINT bk;
INSERT INTO TMP003 VALUES(2,'第二条记录');
select * from TMP003
#回滚到保存点bk
rollback to bk;
commit ;
select * from TMP003;
1 第一条记录

4. SELECT FOR UPDATE

多用户下如下情况下可能会出现异外的情况。此例中用户1,虽然开始select 看到id=5，但是最后因为id=5被另一事务delete了，故update将只更新0行记录。解决此问题方法之一可提前锁定用户感兴趣的行。 会锁定检索的行，除了发出命令的会话外，其它会话都不能改变它们。代价是其它的会话或要修改只能等待（此时其它会话可以查询)。

select * from regions for update;

5.自动提交

自动提交有时叫隐式提交。如DDL,SQLPlus退出等。原因是DDL源码及SQLPLUS退出中已嵌入了一条COMMIT命令。直接点SQLPLUS 右角的‘X’关闭，则不会COMMIT, 将回滚事务。

6.3 回滚和撤消

回滚数据和撤消数据，功能相同，但是管理方式不同。

回滚:
回滚事务指用撤消段中的数据来构造一个与事务发生前相同的数据映象。根据UNDO_MANAGEMENT的参数设置不同，Oracle要么使用撤消段，要么使用回滚段。两者是予盾冲突的。

撤消数据将保持到生成它的事务以COMMIT或ROLLBACK结束为止

select value from v$parameter where name='undo_management';
VALUE
AUTO;
#若不是AUTO，则修改
alter system set undo_management= auto scope = spfile;
#查看撤消表空间
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------------------------------------
UNDOTBS1
select value from v$parameter where name='undo_tablespace';
#查看撤消段大小
select tablespace_name,segment_name,segment_id,status from dba_rollback_segs;
select usn,rssize from v$rollstat;
#查看近来数据库生成的撤消数据量
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
#select value from v$parameter where name='db_block_size'; --8192
select begin_time,end_time,
(undoblks * (select value from v$parameter where name='db_block_size')) undo_bytes
from v$undostat;

6.4 监视和管理撤消数据

撤消段的一个主要特性是其被自动管理。需要保证有足够的撤消空间。

撤消管理的参数:有四个初始化参数可控制撤消:

• UNDO_MANAGEMENT : 默认AUTO,表示启用撤消段的工作。

• UNDO_TABLESPACE

• UNDO_RETENTION ：撤消数据的保留时间相关

• TEMPUNDOENABLED ：存储全局临时表上DML生成的撤消数据。

SQL> show parameters undo;
NAME              TYPE    VALUE
----------------- ------- -------- 
temp_undo_enabled boolean FALSE
undo_management   string  AUTO
undo_retention    integer 900      
undo_tablespace   string  UNDOTBS1
#
select begin_time,end_time,undoblks,activeblks,unexpiredblks,maxquerylen,txncount from v$undostat;

创建和管理撤消表空间（Undo Tablespaces）:

create undo tablespace jinlian_undo datafile  '/u01/app/oracle/oradata/orcl/jinlian_undo.dbf' size 20 M;
select tablespace_name,status,contents from dba_tablespaces where contents = 'UNDO';
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
UNDOTBS1                       ONLINE    UNDO
JINLIAN_UNDO                   ONLINE    UNDO
#
alter database datafile '/u01/app/oracle/oradata/orcl/jinlian_undo.dbf' autoextend on ;
select file_id,file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name like 'JIN%';
#查询表空间情况
select tablespace_name,contents,retention from dba_tablespaces;
#
select name,value from v$parameter where name like '%undo%';
alter system set undo_retention = 1200; #正常是900s,15分钟
select name,value from v$parameter where name like '%undo%';

6.5 撤消数据和重载数据的区别

• 撤消是临时的,而重做是永久的。撤消数据至少在生成它的事在期间是存在的,但可能不长于这段。最终,总是会被覆盖。重做时间是不确定的,首先存在于联机文件中,之后存在于归档日志文件.

• 撤消在逻辑层上进行,而重做在物理层上进行。

• 撤消可以反转更改,而重做可以重复更改。撤消数据提供了反转已提交事务的功能。重做提供了重放已丢失工作的功能

• 撤消位于表空间,而重做位于文件中。撤消数据是数据库中的一个段结构,重做写入操作系统文件.

• 撤消与重做不是相反的。不同的功能。撤消关乎事务的完整性，而重做的作用是防止数据丢失。

闪回查询

闪回查询允许用户查看数据库在以前某个时间的状态。最简单就是用带as of子句查询SQL。

select employee_id,salary from employees where employee_id=206;
update employees set salary=2600 where employee_id=206;
select * from employees as of timestamp (systimestamp - 10/1440)  where employee_id=206  ;

一个常用的场景是误删除，可以反转此操作，如下。

delete from employees where employee_id=206;
#闪回插入,这些是从撤消段UNDO中检索出来
insert into employees ( select * from employees as of timestamp (systimestamp - 10/1440)  where employee_id=206 ) ;

6.6 锁定机制和数据并发管理

借助于记录和表锁机制，可以实现并发访问的串行化。Oracle数据库中的锁是完全自动的。

6.6.1 共享锁与排他锁

若是某一会话正在更新某一行，则此行会被锁定，防止其它的会话对其进行修改，但其它会话可以执行读操作。只有commit或rollback命令结束事务，此锁才会解除。这锁是一个排他(execlusive)锁: 行记录中第一申请排他锁的会话得到锁，其它会话写操作必须等待。对一行或表上的排他锁来说，每次只能有一个会话得到这个排他锁。

共享锁(shared)：

共享锁置于整个表，多个会话可以获得同一个表上的共享锁。表上的排他锁需要执行DDL语句。如果其它会话已在表上放置了共享锁，则此会话就无法对此对象执行修改操作。

在表上放置共享锁的目的是防止其他会话得到此表的排他锁(存在共享锁，其它会话就得到排他锁)

• 所有DML语句至少需要两种锁: 1)更改记录上的排他锁。2)包含受影响记录的表上的共享锁。

• DDL语句需要所涉及对象上的排他锁。只有针对表上的所有DML事务结束，且行上的排他锁，表上共享锁都解除后，才能获得执行DDL命令所需的排他锁。

任何未提交的Insert,update,delete命令都会导致辞表上的任何DDL立即失败。

6.6.2 排队机制

请求锁定需要排队。若某会话请求锁定，但是已有其它会话锁定了行或对象，导致无法获取对象所需要的锁，哪么这个会话将会等待。而当使用锁定的会话解除锁定时，下一个会话将获得授权，这种机制叫"排队(enqueue)"。SELECT xx FOR UPDATE会采用排他模式来选择和锁定记录。

6.6.3 自动和手动锁定

1)自动锁定:

无论执行什么DML语句，在执行过程中，会话会自动在表上加一个共享锁，在影响行上施加独占锁。事务用commit/rollback时，锁会自动释放。执行DDL语句时，会话会自动在整个对象上施加一独占锁，这个锁在DDL整个过程中存在，执行完毕合，自动释放。DDL在内部实现上实际上是对数据字典进行了DML语句。

2)手动锁定

lock table table_name in mode_name mode;
#

锁定模式:有5种模式,相互间的兼容性见表。DML语句需要所涉及对象上的共享锁，以及涉及行上的排他锁。

6.7 监控和解决锁争用(死锁等)

锁定争用: 

当会话请求一行或对象上的锁，需要等待其它会话的锁释放，此会话将挂起。这现象叫锁定争用。会导致数据库性能急剧恶化。死锁（deadlock)是锁定争用的一种特殊情况。

6.7.1 锁定争用的原因

• 多业务同时对相同的记录进行写访问。解决方案是重新设计业务流程开发更好的业务模型。

• 长事务引起。更新某行，但不提交，会导致时间很长的事务。如DBA在没有结束事务时就出去吃饭。

• 编写不好的批处理过程。如财务软件计算总账

• 过高的锁级别。如select xxx for update。

• 不当的锁操作。如为了解决可重复读，用了LOCK FOR UPDATE，但可以用set transaction read only更好。

6.7.2 检测锁定争用

• Database Express 是一处图形界面

• 可以在V$SESSION视图中查看。当前的会话显示为一行，唯一标识符是列的会话标识符(SID) 

select * from  hr.employees where employee_id=206 for update ;
update hr.employees set SALARY=1000 where employee_id=206 ;
#
select waiter.username "Bocked session",waiter.sid , blocker.username "blocking session", blocker.sid,blocker.serial#
 from v$session waiter join v$session blocker on ( waiter.blocking_session=blocker.sid ) order by blocker.sid,waiter.sid;
#
alter system kill session 'sid_xx, serial#';

6.7.3 解决锁定争用

DBA可以止占用过多锁定的会话来解决争用问题。

alter system kill session 'sid, serial#';

6.7.4 死锁:一种特殊情况

两个会话相互阻塞，这两个会话都在等待另一个会话释放其锁定，这种场景称为"死锁"。其通常和不当设计有关。oracle会把死锁的信息写入警报日志。死锁:是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。

死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。

（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。

（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

构建死锁:

#会话1:
create table lockdemo as select * from all_users;
select * from lockdemo where username in ('SYS','SYSTEM');
#会话2:
update lockdemo set user_id=99 where username='SYS';
select * from lockdemo where username='SYS';
#会话3:
update lockdemo set user_id=99 where username='SYSTEM';
#会话2:
update lockdemo set user_id=99 where username='SYSTEM';
在行: 3 上开始执行命令时出错 -
update lockdemo set user_id=99 where username='SYSTEM'
错误报告 -
ORA-00060: 等待资源时检测到死锁
##会话3:
update lockdemo set user_id=99 where username='SYS';

3秒种后，将检查到死锁，并第一个会话会被释放。

在行: 3 上开始执行命令时出错 -
update lockdemo set user_id=99 where username='SYSTEM'
错误报告 -
ORA-00060: 等待资源时检测到死锁

死锁解除:

#-查看
select username,sid,blocking_session, serial# from v$session where blocking_session is not null; #

-查找死锁会话id
select * from v$locked_object;
select session_id from v$locked_object;
--根据会话id查找死锁对象
select sid,serial#,username,osuser fromv $session where sid=58

--清除死锁
#alter system kill session 'sid_no,servial#';
alter system kill session '58,27232';

kill会话结果

#alter system kill session '58,27232';
#另一个窗口会话58将被kill
SQL> update lockdemo set user_id=99 where username='SYS';
update lockdemo set user_id=99 where username='SYS'
       *
ERROR at line 1:
ORA-00028: your session has been killed

6.8 知识回顾

DML数据操作语言:

• TRUNCATE不是DML命令，但是可以删除表中全部的记录。

• TRUNCATE立即生效，并且无法回滚。

• INSERT 可以将一些行，同时插入多个表中。

• 在提交之前,INSERT,UPDATE,DELETE并不是永久生效。

控制事务:

• 事务是逻辑工作单元，可能由几个DML语句组成

• 在提效之前，事务对其他会话不可见。

• 在提交之前，可以回滚事务。

• SAVEPOINT让会话回滚部分事务。

DML和撤消数据生成:

• 所有DML命令都生成撤消和重做内容

• 重做保护对段(撤消段和数据段)所做的所有更改。

• 服务器进程从数据文件读取信息,DBWn针对数据文件执行写操作。

监控和管理撤消数据:

• 实例将在一个指定的撤消表空间中使用撤消段。

• 可以存在多个撤消表空间，但每次只能使用一个。

• 撤消表空间应该有足够的存储量，以便最高效撤消生成速度。

• 撤消表空间数据文件与其他数据文件类似

撤消数据和重做数据区别:

• 撤消操作保护事务

• 重做操作保护块的变更，两者不是相反，而是互补关系。

配置撤消保留时间:

• 撤消数据将保持到生成它的事务以COMMIT或ROLLBACK结束为止,这是"active(活动)"撤消。

• 撤消数据在进入不活动状态后，会保留一段时间，满足长期运行的查询的任何读一致性要求，这是"unexpired(未过期)"撤消。

• "expired(过期)撤消"是读一致性不再需要的数据，在重用撤消段的空间时，将随时重写"expired" 撤消。

• undo_retention参数，配置了撤消保留时间。但是若是撤消空间不足，就可能达不到此目标。

锁定机制和数据并发管理

• 默认的锁定级别是行级别

• 锁定对于所有DML命令是必需的，对于SELECT 是可选的。

• DML语句需要所涉及对象上的共享锁，以及涉及行上的排他锁。

• DDL锁定需要受影响对象上的排他锁。

监视和解决锁定冲突

• 查询v$session视图或使用Database Express可以识别出行锁定导致的阻塞。

• 终止阻塞的事务或停止阻塞的会话，可以解决锁定冲突。

• 死锁将自动解决。

参考:

• OCA/OCP认证考试指南全册。

• https://www.askmaclean.com/archives/oracle-lock.html

• https://www.cnblogs.com/toughhou/p/3778802.html