1.什么是事务

必须具备以下四个属性,简称ACID 属性:

原子性(Atomicity) :事务是一个完整的操作.事务的各步操作是不可分的(原子的);要么都执行,要么都不执行.
一致性(Consistency) :一个查询的结果必须与数据库在查询开始时的状态保持一致(读不等待写,写不等待读).
隔离性(Isolation) :对于其他会话来说,未完成的(也就是未提交的)事务必须不可见.
持久性(Durability) :事务提交完成后,数据库就不可以丢失该事务的结果,数据库通过日志能保持事务的持久性.

 2.事务的开始和结束

2.1 事务开始于

事务采用隐性的方式
起始于session的第一条DML语句(此处的DML指增删改)

SQL> select * from dept2;
-- 起始于session的第一条DML语句(此处的DML指增删改)
SQL> delete dept2 where deptno='40';      
1 row deleted.
-- ADDR是事务对象地址
SQL> select * from v$transaction;
ADDR				XIDUSN	XIDSLOT	XIDSQN	UBAFIL
----------------	------	-------	------	------
00000000EECC3298	2		24		1896	4

2.2 事务结束于

-- 1.COMMIT(提交)或ROLLBACK(回滚)
-- 2.DDL语句被执行(提交)
create alter drop truncate rename comment
-- 3.DCL语句被执行(提交)
grant、
-- 4.用户退出SQLPLUS(正常退出是提交,非正常退出是回滚)
-- 5.服务器故障或系统崩溃(回滚)
-- 6.shutdowm immediate(回滚)

2.3 知识点

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

3. Oracle 的事务保存点功能

3.1 知识点

savepoint命令允许在事务进行中设置一个保存点,该保存点可以控制rollback的效果,即在一个事务中回滚最近部分的dml语句,保留下保存点之前的dml语句,并使事务本身继续执行(知识点).

也就是说回滚到保存点这个动作并不使事务结束

3.2 SAVEPOINT实验

SQL> conn scott/tiger@PDB1;
Connected.
SQL> show user;
USER is "SCOTT"
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> set pagesize 200 linesize 200
SQL> create table emp_pandas as select * from emp;

Table created.

SQL> select count(*) from emp_pandas;

  COUNT(*)
----------
        14

SQL> savepoint sp1;

Savepoint created.

SQL> delete from emp_pandas where empno=7900;

1 row deleted.

SQL> savepoint sp2;

Savepoint created.

SQL> update emp_pandas set ename='pandas' where empno=7788;

1 row updated.

SQL> -- 测试
SQL> select * from emp_pandas where empno=7900;

no rows selected

SQL> rollback to sp1;

Rollback complete.

SQL> select * from emp_pandas where empno=7900;

     EMPNO ENAME                          JOB                                MGR HIREDATE               SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7900 JAMES                          CLERK                             7698 03-DEC-81              950                    30

SQL> -- rollback to XXX 不会使事务结束.
SQL> 

4. SCN的概念

4.1 SCN的概念

SCN全称是System Change Number

它是一个不断增长的整数,相当于Oracle内部的一个时钟,只要数据库一有变更,这个SCN 就会+1,Oracle通过SCN记录数据库里事务的一致性.

SCN涉及了实例恢复和介质恢复的核心概念,它几乎无处不在:控制文件,数据文件,日志文件都有SCN,包括block上也有SCN.

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

4.2 得到当前SCN的办法

SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;

Session altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   23492530

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                23492553

SQL> 

5. 共享锁与排他锁的基本原理:

5.1 共享锁和排他锁

排他锁 :排斥其他排他锁和共享锁.
共享锁 :排斥其他排他锁,但不排斥其他共享锁.

5.2 Oracle数据库锁分类

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

5.2.1 DML锁

(data locks,数据锁) :用于保护数据的完整性.

5.2.2 DDL锁

(dictionary locks,数据字典锁) :用于保护数据库对象的结构,如表、索引等的结构定义.

5.2.3 SYSTEM锁

(internal locks and latches) :保护数据库的内部结构.

5.3 知识点

当用户对某张表做DML操作时会加DDL锁,在事务未结束前,可防止其他用户对该表做DDL操作.初始化参数ddl_lock_timeout可以设定DDL锁的等待时间.**时间过后如果事务仍未结束,**则显示资源正忙.

当用户对某张表做DDL操作时会加DML锁(EXCLUSIVE 排他锁),可以防止其他用户对该表做DML操作

5.4 DML锁的两种类型

Oracle的DML锁(数据锁)包括TM和TX两种

5.1.1 TM

TM是面向对象的锁,表示锁定了系统中的某个对象,在锁定期间不允许其他人对该对象做DDL操作.
TM锁首先产生,目的就是为了实施DDL保护.

5.4.2 TX

TX是面向事务的锁,表示发起了一个事务,是否有事务产生,是根据是否使用UNDO段作为评判标准的.

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

DML操作的TX和TM
行级锁(TX)只有一种;
表级锁(TM)共有五种,分别是 RS,RX,S,SRX,X.

6. 五种TM表级锁的含义:

 

-- 举例
sql													加锁模式	许可其他用户的加锁模式
------------------------------------------------	-------		----------------------------------------
select * from table_name							-			RS, RX, S, SRX ,X
insert, update, delete(DML)							RX          RS, RX
select * from table_name for update					RX          RS, RX

													加锁模式	许可其他用户的加锁模式
------------------------------------------------	-------		----------------------------------------
lock table table_name in row share mode				RS			RS, RX, S, SRX  
lock table table_name in row exclusive mode			RX			RS, RX
lock table table_name in share mode					S			RS, S
lock table table_name in share row exclusive mode	SRX			RS
lock table table_name in exclusive mode				X			无

7. 加锁模式

7.1 第一种:自动加锁

做DML操作时,如insert,update,delete,以及select....for update, 准备做update命令加锁,oracle自动完成加锁,

-- session1
-- scott:用for update加锁,准备做update命令加锁
SQL> select * from emp1 where deptno=20 for update;
EMPNO	ENAME	JOB		MGR		HIREDATE	SAL		COMM	DEPTNO
-----	-----	------ 	----	--------	---		----	------
7369	SMITH	CLERK	7902	17-DEC-80	1600	300		20
7566	JONES	MANAGER	7839	02-APR-81	2975 			20
7788	SCOTT	ANALYST	7566	19-APR-87	3000			20
7876	ADAMS	CLERK	7788	23-MAY-87	1100			20
7902	FORD	ANALYST	7566	03-DEC-81	3000			20
-- session2
-- sys:试探,以防被锁住
SQL> select * from scott. dept for update nowait;
SQL> select * from scott. dept for update wait 5;
-- session1 scott:
SQL> select * from emp1 where empno=7782 for update wait 3;
select * from emp1 where empno=7782 for update wait 3
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired	-- 资源已被占用:执行操作时出现WAIT超时
-- session1 scott:
SQL> select * from emp where deptno=30 for update;
-- session2 sys: 跳过加锁的记录,锁定其他记录..
SQL> select * from scott.emp for update skip locked;
-- 跳过deptno=30.锁定其他行

注意:

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

7.2 第二种:手动加锁

用lock命令以显式的方式加锁.
lock table 表名 in exclusive mode.(一般限于后三种表锁)
观察锁的动态视图v$lock
观察锁的静态视图dba_locks

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

8. 死锁和解锁

8.1 Oracle自动侦测死锁

 

1//session1更新id=1数值

2//session2更新id=2数值

3//session1更新id=2的数值时被锁住,处于等待阶段

4//session2更新id=1的数值时被锁住,造成死锁,信息提示如

5//ORA-00060: deadlock detected while waiting for resource

8.2 管理员如何解锁

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

SQL> select * from v$lock where type in ('TX','TM');
ADDR				KADDR               SID	TY	ID1		ID2		LMODE	REQUEST	CTIME	BLOCK	CON_ID
---------------		----------------	---	--	------	---		-----	-------	-----	-----	------	
00000000F165B670	00000000F165B6E8	26	TX	196625	1880	0		6		414		0		0
00007F07F1C634A0	00007F07F1C63508	26	TM	93815	0		3		0		558		0		0
00007F07F1C634A0	00007F07F1C63508	265	TM	93815	0		3		0		590		0		0
00000000EEC7E838	00000000EEC7E8B8	26	TX	65562	1737	6		0		558		0		0
00000000EECA24A8	00000000EECA2528	265	TX	196625	1880	6		0		590		1		0
SQL> col sql_text format a50;
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=26;
SID	SERIAL#	SQL_TEXT
---	-------	---------------------------------
26	32180	update a1 set id=200 where id=20
SQL> select sid, serial#, blocking_session, username, event from v$session where blocking_session_status= 'VALID';
SID	SERIAL#	BLOCKING_SESSION	USERNAME	EVENT
---	-------	----------------	--------	------------------------------
26	32180	265					SCOTT		enq: TX - row lock contention

-- 也可以根据v$lock视图的block 和request确定session阻塞关系,确定无误后再杀掉这个 session
SQL> ALTER SYSTEM KILL SESSION '26,32180';
System altered.
-- 更详细的信息,可以从多个视图得出,相关的视图有 :
v$session,v$process,v$sql,v$locked,v$sqlarea等
-- 阻塞(排队)从 OEM 里看的更清楚
-- OEM-->Performance-->Additional Monitoring Links->Blocking Sessions(或Instance Locks)