文章详细介绍了Oracle数据库中的事务管理，包括ACID属性、事务的开始和结束、保存点功能以及SCN的概念。此外，还讨论了共享锁与排他锁的基本原理，Oracle的DML和DDL锁分类，并提供了死锁检测及解锁的方法。
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.锁定其他行
注意:
- 对整个表for update 是不锁insert语句的.
- 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 resource8.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)
4691
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
