10. 事务处理

最新推荐文章于 2022-05-27 15:07:52 发布

将军米

最新推荐文章于 2022-05-27 15:07:52 发布

阅读量940

点赞数

分类专栏： SQL标准模块文章标签：数据库 sql

本文链接：https://blog.csdn.net/weixin_46760148/article/details/114160604

版权

SQL标准模块专栏收录该内容

12 篇文章 1 订阅

订阅专栏

第三节事务处理（重点）

传统关系型数据库最大的特征就是具有事务的支持能力，最大的好处是可以保证操作数据的完整性。
在进行数据更新操作的时候，如果某一个更新操作，会导致多张数据表的同时更新，这些更新操作要么一起成功（数据都变），要么一起失败（数据没有变）。事务处理是关系型数据库的最大特征，也是所有性能的瓶颈所在。
3.例如：
比如操作三个表视为一个整体去修改数据，如果第一个表修改成功，第2，3表没有成功就要执行rollback（回滚操作），将修改成功的第1个表，回滚成未修改之前，让三个表保证一致。----表示失败
如果三个表的成功-----》commit-----》表示成功
4.关系型数据库----传统数据库----（Oracle，MySQL，pgsql）----》行和列结构组成
非关系型数据库—不只是行和列的结构
5.为什么会有性能瓶颈？
因为在修改操作某些表的时候，别人不能进行操作。即使修改完某一张数据库表，别人也不能操作，防止第二个张表修改失败，进行rollback使用，换言之，在进行修改操作期间的使用的表，其他人都不能使用，直到这些表的修改操作要么一起成功，要么一起失败，才能用。
这样数据的并发性比较差，一直有人在等待。

3.1事务的控制原理

3.1.1Session会话

在Oracle数据库种，同一个数据库可以连接多次。每一个连接到数据库上的用户在数据库种都会使用session的概念。
1什么是session会话
当用户连接数据库时，数据库角度不知道连接的用户是谁，也不知道用户的状态是什么，但是数据库通过会话来标记这种连接，就称为会话
2.怎么产生会话?
用户A使用sqlplus命令以scott用户对数据库发起连接，用户B使用sqlplus命令也以scott用户对数据库发起连接，而数据库为了区分不同用户的连接，会使用session会话的概念，如果用户A连接数据库就是session1，用户B连接数据库就是session2，以此类推，数据库利用会话进行区分。
每一个连接上数据库的用户就是一个session，一个数据库可以有多个session连接，依靠session来区分不同的用户。
在这里插入图片描述

3.为什么使用session来描述连接
1.在每一个session之中都有属于自己的独立操作
1）对于表的整体更新操作：
Session1选择更新emp和dept
Session2选项更新dept和salgrade
这种操作是应该并行发生还是串行发生？
-----》串行发生，session1一个改完后更新成功了，session2才能继续修改
2）对于表里的行数据的更新操作：
Dept表中有多行数据，假设session1正在更新第一行数据，此时第一行数据已经被session1锁定，在session1解锁之前其他session会话都不能对其继续修改。
若此时Session2也要更新dept的第一行数据，是不能更新的，session2处于等待更新状态，此时session1和session2属于串行关系。
若此时Session3想更新dept的第二行数据，则能正常更新，不受影响。此时session1和session3是并行关系。
在这里插入图片描述

3.1.2事务控制的本质

所谓事务处理的本质就是，在进行事务控制的时候，有一个更专业的称呼ACID原则
1.比如：
某一个更新操作：--------最后的结果：----要么成功，要么失败
对emp表-----》执行更新操作UPDATE
对dept表-----》执行更新操作UPDATE
对salgrade表-----》执行更新操作UPDATE
成功：必须是三个表都更新成功失败：必须是三个表都更新失败
这样做目的是为了保证数据的一致性
2．如何保证数据的一致性或做到如上这样的操作呢？-----》就出现了事务的概念
1）先开始事务，把某一个更新操作放入到事务中----》事务其实就是一个过程
某一个更新操作：
对emp表-----》执行更新操作UPDATE
对dept表-----》执行更新操作UPDATE
对salgrade表-----》执行更新操作UPDATE
通过事务控制让这三个表变成一个整体去操作，此时的最后结果还是要么成功，要么失败
如果三个表的更新操作都成功了-----》提交----》表示事务结束
如果三个表的更新操作都失败了-----》就进行回滚操作-----》也表示事务结束
2）然后在开始一个新的事务，在进行别的操作
3）事务成功是由标志的就是ACID，必须满足ACID原则才算是数据更新成功，如果不满足任何一个就都是失败。
1.ACID原则----》事务的特点
ACID指数据库事务正确执行的四个基本要素的缩写：
原子性（Atomicity）：整个事务中的所有操作，要么全部完成，要么全部失败，不可能停止，在中间某环节，事务在执行过程中发生错误，会被回滚（rollback），到事务开始前的状态，就像事务从来没有执行过一样。
一致性（consistency）：一个事务可以封装状态改变（除非它是只读操作），事务必须始终保证系统处于一致的状态，不管在任何给定的时候并发的事务有多少。
隔离性（lsolation）：隔离状态执行事务，是系统在给定时候内执行的唯一操作，如果有2个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将保证每一个事务在系统中认为是只有该事务在使用系统。
持久性（durability）:在事务完成以后，该事务对数据所做的更改持久的保存在数据库之中，并不会被回滚。----》在成功的更新状态下不支持回滚，持久的改变可能是数据的改变也可能是写入到redo log中。
一个支持事务（transaction）的数据库，必须要具备以上四种特性，否则在事务过程（transaction processing）当中无法保证数据的正确性，交易过程极有可能达不到交易方的要求。
1）MySQL的隔离级别（补充知识）
MySQL中不同的隔离级别决定了事务之间的隔离性
读未提交-----》RU----》此时读取数据块得到的id为100—》出现脏读—》（读取到未提交的数据）
读已提交-----》 RC----》此时读取undo数据得到的id为99----》快照读—》读取undo，解决脏读问题----》会出现幻读和不可重复读问题
-----》幻读（读取到了其他事务已提交的数据，由insert语句产生的）
-----》不可重复读（读取到了其他事务已提交的数据，由delete语句和update语句产生）
如果一旦会话1执行commit命令，id的值由99改为100，此时会话2 读取到的id值也是100，但是这样在一个事务中就属于前后数据不一致—》数据前后不一致由其他事务的update引起—》不可重复读，如果其他事务中由insert语句，导致一个事务中的前后数据不一致—》幻读
可重复读—》 RR----》解决幻读问题（间隙锁），解决了不可重复读问题（undo的MVCC），MVCC是多版本并发控制------》在undo中保存之前的多个快照值。
如果在没有高并发下，只考虑安全性，RR的隔离级别也是很不错的。
序列化----》串行执行事务（一个执行完，执行一个），没有并发事务，不使用。
2）Oracle的隔离级别（补充）
Oracle中也存在事务的概念，事务之间也应该存在隔离性，但是很少讨论Oracle 的隔离级别。----》只用读已提交RC
读已提交—》RC（read committed）----》默认隔离级别----》存在幻读和不可重复读问题，—》一个事务使用insert/update/delete并提交会影响其他未提交的事务，在高并发场景中 RC是很优先的隔离级别
序列化----》串行执行事务，没有并发事务，不使用
只读模式----》只读模式的事务，只能进行select操作，不能进行insert /update/delete操作。

2.总结ACID
1）ACID：一个用户一个用户的更新，每个用户的更新操作彼此独立（互不关联），
更新完成之后的数据要持久化到数据库中
比如用户A对emp和dept—2个表进行更新操作，只有用户A对这个2个表的操作全部做完保存提交后，其他人才能在对这2个表进行操作。
数据持久化就是将数据保存在硬盘中，但在数据库中当中并不是真正的把数据放在硬盘上去实现数据持久化的，而是使用日志先行，先把日志放入到硬盘，就表示真的更新完成。
2）事务的控制，能保证数据的完整性，但也是影响传统关系型数据库的关系瓶颈。
3.Oracle中的事务处理
1）在Oracle中的事务处理命令
在Oracle中提供了很多事务处理命令，但是重点关注两个：
A）commit
Commit：指的是所有的操作进行提交（会进行真正的数据更新）
B）rollback
Rollback：当发现看数据操作产生问题时，需要回滚处理，回滚到上一次的提交点
2）用户更新操作将会记录在更新缓冲区
1.在Oracle中对于事务的支持控制本身就是提供一个操作缓冲区，用户所发出的所有更新操作并不是立刻作用于数据库的。
分析：当用户A对数据库执行UPDATE操作时，不是直接对接数据库，而是在用户A和数据库中会有一个更新缓存区，用户A的update操作是先会被保留到用户缓冲区中的，然后再由更新缓冲区对数据库进行修改，如果此时用户A进行了commit操作，那么在更新缓存区的存放的update操作才会真正的作用到数据库，而用户执行了rollback操作，update操作将会被取消，进行回滚，回到之前的状态，不会对数据库起作用。
在这里插入图片描述

用户如果发现更新没有问题，则提交事务，若发现数据更新有误，则进行回滚。
2.Oracle中若设置关闭事务，这样所有的更新将立刻在数据库中生效，如果关闭了事务就不会存在操作缓冲区，就是用户直接对数据库进行操作，比较危险。
3.Oracle进行事务更新时，事务默认都是打开状态。
A若更新无误
用户发送commit操作，更新缓存区中的更新操作才会真正作用于数据库已实现持久化。
数据库系统又分为实例和数据库文件，使用实例，将数据写入到硬盘中
B 若更新有误
用户发送rollback操作，则会回退到上一个提交点
以上就是Oracle中进行事务处理的主要操作。不仅仅是 INSERT/UPDATE/DELETE会用到事务，SELECT….from 表名 For UPDATE 也会使用到事务锁定。
3）举例
例如1
在会话窗口1，先查看表yynemp数据，然后删除数数据
在这里插入图片描述

2.删除数据----》smith
执行删除相当于打开一个事务
在这里插入图片描述

3.查看数据----》为15行数据，而且没有smith
在这里插入图片描述

在会话窗口2，在此查看数据表yynemp，发现数据还是16行
在这里插入图片描述

这就说明在第一个会话窗口1里执行的删除操作没有commit，如果执行commit进行操作后，2个会话窗口看到的结果就会都是一样数据。
因为在会话窗口1里执行delete操作，就相当于打开了一个事务，我在事务里面对会话窗口1里的yynemp表进行操作时，会话窗口2是感受不到我的操作，这就是事务的一个隔离性。
如果在会话窗口1里的事务执行update yynemp set job=‘netself’ where empno=8899;对此表的hhp行做更新操作，然后再到会话窗口2里的事务执行update yynemp set ename=‘HHP’ WHERE empno=8899;此时会话窗口2的事务就会处于等待状态，因为在Oracle事务中默认执行更新操作时，会将修改的行锁定，其他用户就无法对此行的任何一个字段进行修改。只有等到会话窗口1里的事务执行commit或者rollback后，结束事务，会话窗口2的事务才能进行操作。
只要是执行insert/update/delete操作就会打开事务
例如2
在会话窗口1里执行select * from yynemp；不会打开事务，会话窗口2仍然可以对yynem表执行操作。
但是当会话窗口1里执行select * from yynemp for UPDATE;，也是查看数据表里的数据的信息
SQL> select * from yynemp for UPDATE;
在这里插入图片描述

但在这次去会话窗口 2执行更新操作时就会发现无法执行操作，又陷入等待状态。
因为select * from yynemp for UPDATE;这个语句在它查看数据表里的信息的同时也将此表锁定。因此会话窗口2就不能做关于该表的任何操作。
不建议使用select * from yynemp for UPDATE这样的语句，因为这样的语句是锁定整个数据表的，
例如3
在这里插入图片描述

3.2事务锁

3.2.1什么是锁

在进行数据库事务的控制过程中，注意对于一行数据，若session1正在更新，并且事务并未提交，其他session一定无法进行更新，那么在session2在等待session1 的commit或者rollback的时候，就称为锁-----》是session2是锁定的状态。

3.2.2例如

1.定义2个session分别连接数据库
2.session1现在发送更新执行，更新一条数据
在这里插入图片描述

3.session2 现在也更新发送Smith薪资的更新操作
—>此时session2 会处于等待状态，若此时session1执行了commit或者rollback，则
Session2才会执行更新操作。
4.session1执行rollback释放掉当前锁定的行
----》rollback
5. session2更新操作就会正常发生
6. 在进行锁定的时候除了更新操作之外，也可以采用查询模式
Select * from yynemp where ename =’SMITH’ FRO UPDATE;
在这里插入图片描述

此时就表示当前行被锁定，在当前事务释放之前，该数据不允许被修改。
7. session 2对该行记录进行更新操作
在这里插入图片描述

Session2的操作就会等待
8.对于锁，现在可以理解为session1操作时其他session不能操作，session1释放完资源后，其他session才能操作。

3.3.数据伪列

3.3.1什么是伪列

1.列就是表里的字段，伪列就是不存在的字段，但是可以用，所谓伪列指的就是不存在于表中的列，但是该列又可以直接使用。如：SYSDATE
2例如：sysdate （系统时间）/rownum（行号）
查看yynemp表里的数据
在这里插入图片描述

查看带有系统时间的yynemp表
在这里插入图片描述

查看带有行号的yynemp表
在这里插入图片描述

3.3.2Oracle数据库中的伪列

1.Oracle数据库中2个非常重要的伪列：
Rownum：开发中非常常用
Rowid：数据库分析
1）ROWNUM伪列（核心）
ROWNUM直接翻译为“行号”，在每一次进行查询的时候，如果希望显示出当前的行号，就自动追加一个ROWNUM即可
A）ROWNUM功能
查询第一行记录----只能使用ROWNUM=1，并不能指定任意数字
查询前N行记录----ROWNUM只能查看前N行，没有直接提供查看中间行的能力。
B）例如
1.观察ROWNUM的使用
SQL> select rownum,EMPNO,ENAME,JOB from yynemp;
ROWNUM EMPNO ENAME JOB

 1	 7369 SMITH	 CLERK
 2	 7499 ALLEN	 SALESMAN
 3	 7521 WARD	 SALESMAN
 4	 7566 JONES	 MANAGER

在这里插入图片描述

再次查询：
SQL> select rownum,EMPNO,ENAME,JOB from yynemp where job=‘CLERK’;
ROWNUM EMPNO ENAME JOB

 1	 7369 SMITH	    CLERK
 2	 7876 ADAMS	 CLERK
 3	 7900 JAMES	     CLERK
 4	 7934 MILLER	 CLERK

在这里插入图片描述

使用ROWNUM之后会自动增加一个数据增长列，该行号从1开始，但是一定要注意，这个ROWNUM每一次都会根据查询而自动生成
2.查询第一行数据
SQL> SELECT ROWNUM ,empno,ename
2 FROM yynemp
3 WHERE ROWNUM=1;
在这里插入图片描述

对ROUWNUM 而言，接触到模式数据库时是有一定的帮助的，想要查看一张表的数据，如果使用select * from emp，会显示一个表的所有数据，这样有时候数据表数据多，会不方便查看数据，也可能导致数据库异常。因此使用rownum限定查看的第一行内容进行了解，也可以使用count函数统计表中的数据量。
3.查看emp表中的前5行数据
SQL> SELECT ROWNUM ,empno,ename
2 FROM yynemp
3 WHERE ROWNUM<=5
在这里插入图片描述

4.查看emp表中的第6条到第10条数据
-----》这个功能ROWNUM做不到，但是可以结合集合运算符达到目的
在这里插入图片描述

ROWNUM本身不是固定的数据，而且没有设置支持复杂判断的能力，所有这时候必须借助子查询来处理部分数据的查看。
使MINUX进行集合运算
SQL> SELECT ROWNUM ,empno,ename
2 FROM yynemp
3 WHERE ROWNUM<=10
4 MINUS
5 SELECT ROWNUM ,empno,ename
6 FROM yynemp
7 WHERE ROWNUM<=5;
在这里插入图片描述

2）ROWID（数据库分析使用）
所谓ROWID指的是数据行的唯一的编号，该编号是通过硬盘的存储得到的
当用户A通过客户端工具访问数据库时（数据库上的数据是通过数据库文件的形式保存在硬盘上的）而用户插入的数据，最后会存放在硬盘上，虽然插入的数据存放底层的硬盘里，但也有各自的数据文件，为了定义用户的数据，会给每个数据文件一个编号rowid
一定要清楚，每一个ROWID 绝对可以定义一行数据记录，找到了rowid就找到了一个行数据
A）ROWID的基本结构（4段）
这些字母都会变的不是固定的
数据的对象编号：AAAVpK
数据保存的文件编号：AAE
数据保存的文件块编号：AAAAIP
数据保存的行号：AAC
在这里插入图片描述

B）例如
a）观察ROWid的组成
SQL> SELECT ROWID,deptno,ename FROM yynemp;
在这里插入图片描述

每一行都记录这唯一的ROWid编号，且要想定位某一个数据行，可以通过ROWid实现
b）通过ROWID 找到一行数据
SQL> SELECT *
2 FROM yynemp
3 WHERE ROWID=‘AAAVpKAAEAAAAIPAAA’;
在这里插入图片描述

C）ROWID的面试题
要求：现在有一张数据表，但是该数据表在设计初期考虑不周，导致了数据表中存在大量的完全重复的数据，要求删除重复数据，（不是全部删除，重复的内容保留一个，最早的一个记录）
先准备有重复数据的表
1.将dept表的内容复制为yyndept表
SQL> create table yyndept as select * from dept;
在这里插入图片描述

2.查看yyndept表内容：
SQL> select rowid,deptno,dname,loc from yyndept;
在这里插入图片描述

3.向yyndept表追加重复数据
insert into yyndept (deptno,dname,loc)values(20,‘RESEARCH’,‘DALLAS’);
insert into yyndept (deptno,dname,loc)values(20,‘RESEARCH’,‘DALLAS’);
insert into yyndept (deptno,dname,loc)values(30,‘SALES’,‘CHICAGO’);
在这里插入图片描述

问题分析：
在这里插入图片描述

观察此表，我们不能通过deptno，dname，loc列来进行删除，实际上每行的区别就在ROWID
首先想办法查询出所有应该被保留的数据（被保留的数据一定是最早增加的）
数据行的增长是由小到大的，所以最早的rowid应该是最小的
解决问题：
可以采用分组的形式处理
Select deptno，dname，loc min（rowid） from yyndept group by deptno，dname，loc；
在这里插入图片描述

保留最早的ROWID即可，删除其他不是最早的rowid的所有信息

SQL> delete from yyndept
2 where ROWID NOT IN (
3 select min(rowid)
4 FROM yyndept
5 GROUP BY deptno,dname,loc
6 );
在这里插入图片描述

这种情况不会在真实的开发环境出现
查询删除后的结果-------》数据就又回来了
在这里插入图片描述

2数据分页
1）数据分页显示处理
是可以让表中的数据信息按照部分的模式来完成
若数据库中有1千万行数据，但在任意新闻客户端，读取新闻时多少采用了部分读取的模式，那么这样的过程就称为分页显示。
2）Oracle数据库中分页显示的基本语法
SELECT *
FROM (
SELECT 查询字段1，查询字段2.。。。。,ROWNUM rn（rownum的别名）
FROM 数据表
WHERE ROWNUM <=currentPage * lineSize
）temp（临时表名）
WHERE temp.rn>( currentPage-1)* lineSize;
其中currentPage指的就是当前所在页，而lineSize描述的是每页显示的数据行数
3）例如
A）查询emp表中6-10的记录
lineSize=5（一页有5行） currentPage=2（分2页）
SQL> SELECT *
2 FROM (
3 SELECT ROWNUM rown,empno,ename
4 FROM yynemp
5 WHERE ROWNUM <= 10
6 ) temp
7 WHERE temp.rown>5;
在这里插入图片描述

A）查询emp表中11-15的记录
lineSize=5（一页有5行） currentPage=3（分3页）
SELECT *
FROM (
SELECT ROWNUM rown,empno,ename
FROM yynemp
WHERE ROWNUM <= 15
) temp
WHERE temp.rown>10;
在这里插入图片描述

以后进行程序开发的过程中，分页操作是必须的数据库开发技术。

将军米

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
10. 事务处理

第三节事务处理（重点）3.1事务的控制原理3.1.1Session会话3.1.2事务控制的本质3.2事务锁3.2.1什么是锁3.2.2例如3.3.数据伪列3.3.1什么是伪列3.3.2Oracle数据库中的伪列传统关系型数据库最大的特征就是具有事务的支持能力，最大的好处是可以保证操作数据的完整性。在进行数据更新操作的时候，如果某一个更新操作，会导致多张数据表的同时更新，这些更新操作要么一起成功（数据都变），要么一起失败（数据没有变）。事务处理是关系型数据库的最大特征，也是所有性能的瓶颈所在。3.例
复制链接

扫一扫