Oracle之事务和锁

那我们现在讲事务的东西,无论我们是什么数据库,只要是关系型数据库,都有ACID,那ACID原子性,一致性,隔离性,持久性

分别的场合在这里说


1. 首先原子性,一个事务必须是一个完整的操作,事务的各个部分的操作是不可分割的,原子的特性,要么执行,要么都不执行

很简单的场景就是银行转账,一个减100,一个加100,现在好像都不用这种方式了,看到支付宝和余额宝转账的问题,比如A叫

支付宝,然后B就是叫余额宝,他们之间要有一个转账的策略,其实支付宝肯定是一个系统,他们是异构的系统,编码的时候可能

不是一个时间点去开发的,比如举个例子,支付宝是05年开发的,余额宝是07年开发的,如果异构的系统如果想做异构的分布式,

这种分布式事务,就好像银行转账一样,一个是交通银行,一个是其他的银行,比如说招商银行,可以这么理解,传统的做法呢

比如你要靠关系型数据库古老的做法,采用二次提交事务,这种方式,这个我们以后讲MQ的时候会说,消息中间件,消息中间件去

解决这种分布式事务,其实事务要解决就一个问题,就是保证事务的一致性,其实分布式事务要考虑的就是一个问题,就是数据的

一致性,你必须保证你这边减了1千块钱,那边得有1千块钱,如果没有的话,你必须得回滚掉,现在是两个系统之间,你应该怎么去做,

这个事情以后讲MQ的时候再说吧,就是关于原子性,如果是本地事务的话,肯定满足原子性了,一个Spring切面,里面有很多的Service,

让他去怎么怎么样,做一个DAO,让他去保证事务的原子性


2. 一致性呢是一个查询的结果必须冲数据库查询的开始,就是你开始想数据库发起一条SELECT的时候,从那一点开始记录,读不等待写,

写不等待读,这是我们非常关键的一个特性,就是一致性,在我们讲并发编程的时候,比如我又一张表,这张表可能有很多数据,我用户

A9点这一刻,9点00分00秒的时候,去做了一条SELECT的查询,到一张数据库里面去查一张大表,100多万条数据,查的数据在90多万的

时候,然后中间有一个client端,把这个改了,可能他查的时候需要10分钟,10分钟才能查完,这是9点开始查,9点10分把数据返回,

然后我这边9点05的时候,去把这个数据改了,这个数据原先可能是100,然后我直接执行update和commit,我把它改成200了,那么最终

9点10分的时候,A用户看到的一定是100,是这个老的值,就是没改之前100的这个值,他一定看不到这个新的100的这个值,这个就

体现了我们数据库的一致性,当你9点这一刻这个时间点发起的SELECT语句,那么你看到的数据一定是9点这一点上所有的数据的变化,

9点01分的你都看不到,这就是一致性读的概念,或者还有一致性写,然后继续往下看吧,场景刚开始已经描述好了,ORACLE事务有一些

undo的概念,这个以后再说吧,再往下看


3. 隔离性无非就是两个SESSION,比如我当前用了一个SCOTT,登陆了我们的用户,我要操作SCOTT里面的一张表,比如我又有一个

SCOTT2,他又是一个用户,他也去登陆了ORACLE服务器,那很明显现在是两个SESSION,你无论说你在里面玩出什么来,SELECT,UPDATE,

只要你不COMMIT的话,他们两个之间是两个SESSION,所以严格保持数据的隔离特性,只要你不COMMIT,你随便改随便删,这都没问题,

一旦COMMIT了以后,他们两个的数据就可见了,不COMMIT的话,你在你自己的SESSION中随便去玩,这个就是隔离的特性,没有任何影响


4. 然后还有持久性,事务一旦被提交,数据库就不可以丢失这个事务的结果,数据库通过日志能够保持事务持久的特性,然后场合就是

说,事务提交是一个不可逆的操作,提交数据是由内存数据刷到磁盘上的,这个过程的快慢和性能有关,和你的机器的性能有关,那么

ORACLE用的是redo,用redo日志来记录到磁盘上的,事务一旦你commit以后,我一commit的瞬间,有一个redo先记录懂啊我这个日志,

然后记录到日志以后,开始做insert,开始去做了,你commit的一瞬间,你的这个redo日志肯定记录好了,哪怕你下一秒,下一刻,

整个机器挂了,都断点了都没关系,ORACLE重启再恢复的时候,找到之前你操作的数据,然后把没干完的事给干完,这个可以去做到的,

那这个就是事务的4个特性,咱们简单的讲一下,这个都很简单吧,然后继续往下看

这个事务的开始和结束,事务采用隐式的方式,然后起始于SESSION的第一条DML语句,就是我们执行UPDATE,DELETE

的时候,执行的就是DML语句的,那就是直接开启了事务了,你可以通过这个表去查看事务,当前是SCOTT,我们现在

create一个table,不在原表上去操作,create table emp1 as select * from emp;然后select * from emp1;

刚才我们新建了一张表,叫emp1,emp1表里是有这么多数据的,我这边还是用SCOTT去操作的,update emp1这个表,

set这个sal吧,这个字段,现在等于这个888,where条件就是empno,等于这个7369,然后这个是在我自己的SESSION,

update emp1 set sal = 888 where empno = 7369,我就这么去做了,现在我肯定没有去敲COMMIT,rollback

就回滚了,在我执行这种DML操作的时候,我用这个v$transaction去做,select * from v$transaction,咱们

conn system/tiger,然后我去select * from v$transaction,应该是有事务的,v$transaction正常来讲是能

看到事务的,用户不对,应该是用谁啊,scott没有查$v的权限,我说的是什么事啊,刚才update语句一旦写了以后,

这个事务还是相当于开启的,这个事务开启了之后,你就看v$v$transaction,其实这张表里是有一条记录的,

有事务的记录的,把dba的权限赋给SCOTT,用cmd能不能看到,就是用sysdba最大的用户,sqlplus / as sysdba:

select * from v$transaction,他这里面也不行啊,用一下这个,conn / as sysdba,用这个system登陆一下,

select * from v$transaction,还是不行,先不管他了,是不是想告诉你一件事,可能是用户不对,我重新再连一下,直接用

Command Window,可能是我连的时候不是sysdba,select * from emp1看一看,update emp1 set sal = 888 where 

empno = 7369,然后这边是事务已经开启了,这个时候select * from v$transaction就ok了

我重新登陆了,默认是普通用户,我们要选sys as dba,才能看到这个结果,也就是说用户刚才不对了,这一块就产生一个

transaction了,就是这个事务了,这个事务有事务ID号,事务一直存活着,只要是你做了这件事情了,就会有一个事务,事务也就必然

会产生一个锁

这个锁我们可以重置去查

这个锁就是在这儿,他必然会产生表锁和行锁,TM和TX,只要产生事务,肯定会有这个锁,这个一会再说,

我先再把Command Window开起来,我再用conn as sysdba,选择system,Connect as 不是普通的角色,而是SYSDBA,然后密码,

然后你选择的数据库实例

我应该这么连,这就OK了,然后我们再看那个语句,查看事务的语句,select * from v$transaction,事务还是一直存在的

因为我这块没有提交,如果我这块commit,commit就是成功了呗

就是事务已经结束了,我们再看一下

这回这个事务就已经不存在了,那么也就是有一个问题就是,一旦我们执行DML操作,肯定会有事务,commit是提交,

rollback是回滚,这个都很简单,还有一些产生事务的,DDL语句的执行,DCL语句的执行和被提交,都会产生事务,然后用户退出

sqlplus,正常退出的时候是提交事务的,非正常退出的时候是回滚的,机器故障了是回滚的,shutdown immediate也是回滚的,

这可能是相当于简单的操作了,好长时间不用我都有点忘了,这是关于事务的问题,其实你只要记住一件事,就是执行DML语句的时候,

必定会产生一个事务,产生事务了,一定会有一个锁的概念,就是lock锁,在这儿还是说啊,锁大体上分很多种,大体上分两大种

一种是共享,一种是排他,共享锁和排他锁有什么区别呢

1. 排它锁就是独占,就是占掉了谁也进不来,就好像是一个多线程的时候,一个人进到线程里执行了,前面加一个sycnhronized,

其他的人都进不来了,排斥其他排斥和共享锁,比如举个例子,我现在有一把锁,这把锁的颜色是红色的,这把锁的颜色是绿色的,

红色就是排它锁,绿色是共享锁,当我现在执行一个语句的时候,上了这把锁了,如果这把锁是排他锁的话,他就是排斥了其他的

排他锁和共享锁,都是排斥的,如果共享锁了呢,只是排斥其他的排它锁,但是不排斥其他的共享锁,其实这个概念跟咱们这个叫

之前我们学线程的时候,就是Thread的时候有一个概念,读写共享锁的,还记得吧,就是读读共享,写写互斥,就是我可能用ReentrantLock

他有一个东西叫readLock,还有一个叫writeLock,这两把锁吗,一把是读锁,一把是写锁,就是读和读是共享的,但是读和写肯定是互斥的

然后写和写也是互斥的,其实对于ORACLE里面,也是一个通用的一个概念,就是排它锁和共享锁,共享锁就是读锁,排它锁就是写锁,

可以简单的这么理解一下,然后锁的类型大体上分很多种,比如TX行级锁,TM表级锁,我们一般来讲,日常所用的DML操作,都会产生事务和锁,

有事务必有锁,查看事务的语句,select * from v$transaction,查看锁的语句,select * from v$lock,然后还有一些DDL锁,

就是dictionary locks,就是数据字典锁,他用于保护数据对象的结构,比如表,索引,等等这些结构的,比如SYSTEM锁,锁的用途,

只有有事务才会产生锁,保证数据的完整性,一致性,还有以及正确性,锁的作用是做这个事情的,其实你可以和JAVA里的锁做对比一下,

然后锁里面有很多种

行共享,行排他,还有什么共享锁,还有什么共享排他锁,还有排他锁,很多啊,就是这里面分这么多种,RS,RX,S,SRX,X

如果你要做ORACLE DBA的话,没有这个东西,在OCA的时候,最基础入门的时候,这些都得要讲的,而且你都得要会的,就是对于锁的

类型,每个锁是干什么用的,就是简单知道这个事就行了,我不会说讲太多,然后这里还有一个加锁的模式

然后这里还有一个加锁的模式,可能会用到吧,咱们可以使用select * from 表,where条件加上for update,去给这个

东西上锁,select * from emp1 where deptno = 10 for update;去给你的SQL去上一把锁,这个语句自动加锁,在做DML的

时候,这些语句都会自动加锁,刚才其实咱们都看到了,你只要是执行一个DML语句的,在事务里面咱们看到一个记录,在锁里面

咱们也看到一个TX和TM,一个行锁,一个表锁,但是在SELECT的时候,其实也是可以去加锁的,就是你去加一个for update做这个

事情,其实别人就不能够碰了,也就是整个意思,另外我做一个这样的事情,当前这个用户是SCOTT,这个用户是DBA,咱们拿DBA

去观察数据信息,拿SCOTT去做操作,当前我肯定是emp1这张表,我一查询的时候

你会发现当前这个事务已经开启了,for update,修改其部门为10的记录会被锁定,就是你已经加了一把锁了,你还想去

修改部门为10的记录,那就会被锁定,我们可以尝试的去修改记录,是否被加锁,你可以去做一些试探,可以有这三种方式

做试探,举个例子吧,其实有的时候你使用ORACLE的时候,你总会觉得什么死锁啊,其实在ORACLE里面是不存在死锁的问题,

在ORACLE里面如果真的发生死锁的话,ORACLE会给你解决的,其实以前工作的时候也总是发现什么啊,这边就是这个表,

就是谁也进不来了,就是锁上了,然后你就发现你的SESSION就卡到这儿了,然后初级的程序员总觉得是怎么,是不是咱两

操作产生冲突了,是不是产生死锁了,其实在ORACLE里面是不存在死锁的概念的,那为什么会有这种情况呢,一定是人家做了

一个吧第10号部门的这几条记录都给我进行for update了,就是我要更新,锁已经占着了,然后其他那个人的客户端想改

10号部门的其中一条记录,然后他就发现那个SESSION就一直卡到那儿,就一直等着了,因为这个哥们for update还没有做完

事,然后另外那个人只能在那里等着了,等你这边把事务提交了,你做完这个操作了,然后他才能真正的去修改,所以这个等待的

过程就相当于什么啊,就是那个排队的过程,没有死锁的概念啊,咱们做一下测试你就知道了,那刚才我还是要查这个system

我们发现这里面有一个TM和TX了,比如我提交

咱们再次看一下

看一下我这个操作,就是select * from v$lock;

最后几块,是没有任何TX,TM字样的,TX表示行锁,TM表示表锁,是没有的,如果我刚才要执行这个语句的时候,for update

的时候

你会发现我在看一次

它会产生后面两个东西,一个是TM,一个是TX,会产生这两个东西,那既然产生这个东西了,那我还有个哥们,我们再来一个

Command Window,也是SCOTT,他现在就像做这个事情,我现在想做update,update emp1表,set sal,咱们还是还原回来,也

还原不回来了,等于10000,4个0,update emp1 set sal=10000,where条件就是7782,就是empno=7782,

update emp1 set sal = 10000 where empno = 7782,回车

去执行ORACLE的时候,有的人这块怎么就卡死了呢

是不是我死锁了啊,或者怎么怎么样,其实不是,因为你这个SQL语句,在那边排队等着呢,你必须等这哥们for update完事

之后,你才能访问

我这里一个commit

紧接着他就row updated,一条记录进行update了,其实就是这个过程,其实为了避免锁一直占用,其实我们写SQL的时候,

尤其你用ORACLE的时候,用一些手段避免这个事,本来你想去数据库做一条查询,然后查询以后,那边的人,包括银行的一些

业务的时候,他肯定是要锁表的,for updated,执行了这么一个操作之后,for update,下面一个client端,他想操作同一条

记录的时候,他只能在外面等着,那就意味着什么啊,可能用户的页面要点更新,点修改,这个操作的时候,页面可能有按钮,

点的时候就一直卡到这儿了,卡死到这儿了,那这个界面肯定是不行的,原因不是咱们程序的事情,其实核心的原因是因为

那边for update了,这边只能在这边等着,其实是由于这个事,可以解决的方案其实有很多,有什么方案呢,可以去触碰他一下

简单的说怎么去触碰,咱们还是以这条语句为例,以后你是做一些比较严谨的业务的时候,更新的时候是不允许其他的人去修改,

也就是避免并发,我一定要在后面加for update,我数据是安全的,把这三条记录锁住

如果其他的client端非得要访问这三条,那你可以这么去做,怎么去做呢,试探他一下,试探的方式写了三种,

第一种叫做不等待,三面这三条记录已经被我锁住了,7782这条记录已经被我锁住了,我就select * from emp1 where 

empno = 10 for update nowait;这一块我是可以这样去做的,就是不等待

还有一个是等待几秒,还有一个是跳过已经锁定的东西了,我可以做这个事情,应该不是在这儿,就是我再去查他的时候,

我再次触碰去查的时候,他告诉我,not wait,什么意思,就是不等待吗,我再去查的时候,那个哥们已经把这三条记录占着呢

那怎么办,其他人想操作查这个数据的时候,他想查的时候是不等待,那这样的话,那个客户端,一进来,进来的时候,

他马上就能返回去,告诉你这个用户说,对不起系统在忙,请稍后再试,可以用这种策略,还有什么啊

还有这种,让他等几秒,最大你可以容忍几秒,wait 5秒钟,你可以数吧,5秒钟之后,肯定也会出现这个错误

就是wait超时,你可以指定一个时间

还有一种是跳过,我看一下,这个语法你看一下,首先select * from 条件,现在条件变了,变成什么了,变成job等于销售,

CLEAR销售这个职位,然后for update 然后skip locked,就是跳过被锁定的记录,select * from emp1 where job='CLEAR'

for update skip locked,跳过了MILLER这条记录了,7934的这条记录,因为7934的这个job就是这个,他在我的一个用户已经

update了,正常来讲我能够查到4条记录,因为有4条记录是CLEAR这个职位的,因为已经有个用户已经占着这条记录了,所以说你可以

去做这件事情,skip locked,就是跳过的记录就不查出来,要查没被锁的记录,其实在操作的时候呢,有一种概念就是,叫做表霸,

就是DBA一上来就把所有的表给霸占了,其他人谁也别想玩了,我一上来就select * from 表 for update,谁也别想玩我里面的数据

反正我们的那个DBA一上来就做这个事,这都是一些简单的特性吧,反正你在做一些银行核心业务的时候,都可能会用到for update 

这些参数,为了严谨

然后这块后面还可以做什么事啊,如果这锁占用的时间太长了,你如果可以做什么事,你有管理员用户的话,

你可以把那个表霸for update那个进程给他干掉,SESSION就给他kill掉,可以去把它的SESSION杀掉,你能不能别玩了,

你都占用一个多小时了,我可以把它kill掉,那这个kill也是很简单的,就是利用这个语句,可以去kill掉

select sid,serial# from v$session where sid = 170,alter system kill session 'sid,serial',做一下测试吧,

比如说,还是他,咱们还是要做for update

那现在肯定是有一个SESSION产生了,有一个事务产生了,然后我现在就像把这个kill掉怎么办,那我就利用管理员用户,

我去做这个事情,首先我要查这个,我要select,首先我要看哪个sid占用的时间太长了,要去看v$lock这个表,到这个表里面

去找到这个sid,然后执行select sid,serial# from v$session where sid = 100这个SQL语句,然后再把serial这个东西查

出来,然后再kill掉,这个挺麻烦的,咱们来做一下操作吧,首先是v$lock这张表里面,这个哥们肯定是一直占着这几条数据的,

我如果用管理员登陆的话

我就看到了这141这个,这个SID,它是一直锁着表的,它是一直占着SESSION的,这个是SID,这141这个哥们我就想把它删掉,

那怎么办,就这样去做,首先就是select sid, serial# from v$session where sid = 141,首先我要做的事情是查询v$session

这个sid,sid是141呗,查询出来

找到这两条记录,一个叫做SID,一个叫SERIAL#,然后我去执行系统的alter权限,我去做什么啊,我去把这个kill掉,

sid是141,然后这个serial是40,然后我一回车,altered

然后你会发现,其实这个时候这个用户的SESSION就已经掉了,比如我commit一下,其实就已经不好用了,你的connection

就已经lost,就是已经是掉线的状态了,因为我已经用系统管理员已经把你当前的SESSION给kill掉了,所以你会发现他是这种情况的,

只要把这个SESSION给kill掉了,其他的用户都可以去访问了,都是可以正常的去访问了,这个就是你在工作中可能会碰到这种问题,

有的时候你必须得用for update,下面显示commit completed,其实这块已经掉线了,我操作的时候他已经掉线了,你放心吧,我用管理员

去kill掉的时候,已经是掉线了,因为已经把SESSION强杀掉了,kill掉了