oracle database about Business and Lock ------事务与锁

淩亂°似流年 2022-06-01 10:13 186阅读 0赞

## 事务 ##

必须具备以下四个属性，简称ACID 属性：  
原子性（Atomicity）： 事务是一个完整的操作。事务的各步操作是不可分的（如原子不可分）；各步操作要么都执行了，要么都不执行。  
一致性（Consistency）：1）一个事务结束之后，所有会话发起的查询所看到的该事务的结果都是一致的（commit后的查询有同样的结果）。  
2）一个查询的结果必须与数据库在查询开始时的状态保持一致（读不等写，写不等读）。  
隔离性（Isolation）： 某个会话正在进行的事务所引起的变更对于其他会话来说必须不可见。  
持久性（Durability）： 事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。

--------------------

## 事务的开始和结束 ##

事务采用隐性的方式，起始于session的第一条DML语句，

事务结束于  
1. COMMIT（提交）或ROLLBACK（回滚）  
2. DDL语句被执行（提交）  
3. DCL语句被执行（提交）  
4. 用户退出SQLPLUS（正常退出是提交，非正常退出是回滚）  
5. 服务器故障或系统崩溃（回滚）  
6. shutdowm immediate(回滚）

*在一个事务里如果某个DML语句失败，之前其他任何DML语句将保持完好，而且不会提交！*

savepoint命令允许在事务进行中设置一个标记（保存点），回滚到这个标记可以保留该点之前的事务存在，并使事务继续执行。  
savepoint sp1;  
rollback to sp1;

*rollback to XXX 后，之前的事务不会结束。*

--------------------

## SCN ##

SCN全称是System Change Number  
它是一个不断增长的整数，相当于Oracle内部的一个时钟，只要数据库一有变更，这个SCN就会增加，Oracle通过SCN记录数据库里事务的一致性。SCN涉及了实例恢复和介质恢复的核心概念，它几乎无处不在：控制文件，数据文件，日志文件都有SCN，包括block上也有SCN。

实际上，我们所说的保证同一时间点一致性读的概念，其背后是物理层面的block读，Oracle会依据你发出select命令，记录下那一刻的SCN值，然后以这个SCN值去同所读的每个block上的SCN比较，如果读到的块上的SCN大于select发出时记录的SCN，则需要利用Undo得到该block的前镜像，在内存中构造CR块(Consistent Read)。

获得当前SCN的两个办法：

SQL> select current_scn from v$database;
    CURRENT_SCN
    -----------
        7222678

SQL> select dbms_flashback.get_system_change_number from dual;
    
    GET_SYSTEM_CHANGE_NUMBER ------------------------
     7222708

有两个函数可以实现SCN和TIMESTAMP之间的互转  
scn\_to\_timestamp  
timestamp\_to\_scn

select scn\_to\_timestamp(current\_scn) from v$database;

--------------------

## 锁 ##

排他锁，排斥其他的排他锁和共享锁。  
共享锁，排斥其他的排他锁，但不排斥其他的共享锁。

因为有事务才有锁的概念。Oracle数据库锁可以分为以下几大类：

DML锁（data locks，数据锁），用于保护数据的完整性。  
DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义。  
SYSTEM锁（internal locks and latches），保护数据库的内部结构。

*当一个用户对某表做DML操作时，也会加DDL锁，这样在事务未结束前，可防止另一个用户对该表做某些DDL操作。初始化参数ddl\_lock\_timeout可以设定了DDL锁的等待时间。时间过后如果事务仍未结束，则显示资源正忙。*

*当一个用户对某表做DDL操作时，也会加DML锁（EXCLUSIVE 排他锁），这样可以防止另一个用户对该表做DML操作*

Oracle的DML操作（insert、update、delete），它包括两种锁：TX（DML行锁）和TM（DDL表锁）。

TX 是面向事务的DML行锁，它表示你锁定了表中的一行或若干行。update和delete操作都会产生行锁，insert操作除外。  
TM 是面向对象的DDL表锁，它表示你锁定了系统中的一个对象，在锁定期间不允许其他人对这个对象做DDL操作。目的就是为了实施DDL保护。

比如一个update语句，有表级锁（即TM)和行锁（即TX锁）。Oracle是先申请表级锁TM（其中的RX锁）, 获得后系统再自动申请行锁(TX), 并将实际锁定的数据行的锁标志置位（即指向该TX锁）。

对于DML操作

行锁(TX)只有一种  
表锁(TM)共有五种，分别是 RS,RX,S,SRX,X。

五种TM表锁的含义：

1.  ROW SHARE 行共享(RS)，允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁
2.  ROW EXCLUSIVE 行排他(RX)，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁
3.  SHARE 共享(S)，不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁
4.  SHARE ROW EXCLUSIVE(SRX) 共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁
5.  EXCLUSIVE (X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何排他锁。
    
    ![这里写图片描述][SouthEast]

--------------------

## 加锁模式 ##

自动加锁

做DML操作时，如insert，update，delete，以及select….for update由oracle自动完成加锁

session1 scott: *//用for update加锁*

SQL> select \* from dept1 where deptno=30 for update;

DEPTNO DNAME          LOC
    ---------- -------------- -------------
          30   SALES          CHICAGO

session2 sys: *//不试探，被锁住*

SQL>select * from scott.dept1 for update;

session2 sys: *//试探，以防被锁住*

SQL>select * from scott.dept1 for update nowait;
    SQL>select * from scott.dept1 for update wait 5;

跳过加锁的记录，锁定其他记录

SQL> select * from scott.dept1 for update skip locked;

*对整个表for update 是不锁insert语句的。*  
*wait 5：等5秒自动退出。nowait：不等待。skip locked：跳过。都可起到防止自己被挂起的作用。*

人工方式加锁，用lock命令以显式的方式加锁。

lock table 表名 in exclusive mode.（一般限于后三种表锁）

观察锁的动态视图v$lock  
观察锁的静态视图dba\_locks

SQL>select * from v$lock;
    SQL>select * from dba_locks where session_id=149;

--------------------

## 死锁和解锁 ##

可以根据以下方法准确定位要kill session的sid号和serial\#号

SQL> select * from v$lock where type in ('TX','TM');
    
    SQL> select a.sid,a.serial#,b.sql_text from v$'session a,v$sql b where a.prev_sql_id=b.sql_id and a.sid=127;
    
    SQL> select sid,serial#,blocking_session,username,event from v$session where blocking_session_status='VALID';

--------------------

select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id　=　o.object_id and l.session_id=s.sid;

select a.username,a.sid,a.serial#,a.logon_time,
     A.SQL_ID from v$locked_object b,v$session a
     where b.session_id=a.sid order by a.logon_time;

也可以根据v$lock视图的block 和request确定session阻塞关系,确定无误后再杀掉这个session

SQL>ALTER SYSTEM KILL SESSION '127,2449';

*相关的视图有：vsession,v  s e s s i o n , v process,vsql,v  s q l , v locked,v$sqlarea*

*阻塞(排队）EM ： EM–>Performance–>Additional Monitoring Links–>Blocking Sessions(或Instance Locks)*

[SouthEast]: /images/20220601/9c7769287ce949589e0e9d0787db24d1.png