Oracle 的事务和锁

最新推荐文章于 2023-03-07 11:01:26 发布

数据架构

最新推荐文章于 2023-03-07 11:01:26 发布

阅读量277

点赞数

本文链接：https://blog.csdn.net/u011868279/article/details/100550762

版权

1.1 什么是事务？

事务必须具备原子性，一致性，隔离性，持久性的四种属性，简称ACID属性：

原子性（Atomicity): 事务是一个完整的操作。事务的各步操作不可分的（原子的），要么全执行，要么全不执行。

一致性（Consistency）: 一个查询的结果必须与数据库在查询开始时的状态保持一致（读不等待写，写不等待读）。

隔离性（Isolation）:对于其他会话来说，未完成的（也就是未提交的）事务必须不可见。

持久性（Durability）:事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。

1.2 事务的开始和结束。

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

1.2.2 事务结束于：

1）COMMIT（提交）或 ROLLBACK（回滚）

2）DDL 语句被执行（提交）

3）DCL 语句被执行（提交）

4）用户退出 SQLPLUS（正常退出是提交，非正常退出是回滚）

5）服务器故障或系统崩溃（回滚）

6）shutdowm immediate(回滚）

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

1.3 Oracle 的事务保存点功能

savepoint 命令允许在事务进行中设置一个标记（保存点），这个标记可以控制rollback的效果，即在一个事务中回滚掉最近的部分DML语句，保留下保存点的DML语句。并使事务本身继续进行（考点）。也就是说回滚到保存点这个动作并不使事务结束。

SAVEPOINT 实验

savepoint sp1;

delete from emp1 where empno=7900;

savepoint sp2;

update emp1 set ename='timran' where empno=7788;

select * from emp1；

rollback to sp2;

select * from emp1；

rollback to sp1;

//rollback to XXX 不会使事务结束。

1.4 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）。

SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1220187

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
1220202

1.5 共享锁与排他锁的基本原理：

排他锁，排斥其他排他锁和共享锁。

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

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

DML锁（data locks,数据锁），用于保护数据的完整性。

DDL锁（dictionary locks,数据字典锁）,用于保护数据库对象的结构，如表、索引等的结构定义。

SYSTEM锁（internal locks and latches）,保护数据库的内部结构。

考点： 1）当一个用户对某表做 DML 操作时，也会加 DDL 锁，这样在事务未结束前，可防止另一个用户对该表做 DDL 操作。初始化参数 ddl_lock_timeout 可以设定了 DDL 锁的等待时间。时间过后如果事务仍未结束，则显示资源正忙。 2）当一个用户对某表做 DDL 操作时，也会加 DML 锁（EXCLUSIVE 排他锁），这样可以防止另

一个用户对该表做 DML 操作

我们探讨的是 Oracle 的 DML 锁(又叫数据锁），它包括 TM 和 TX 两种

TM 是面向对象的锁，它表示你锁定了系统中的一个对象，在锁定期间不允许其他人对这个对象做 DDL 操作。TM 锁首先产生，目的就是为了实施 DDL 保护。 TX 是面向事务的锁，表示发起了一个事务，是否有事务产生，这是根据是否使用 UNDO 段作为评判标准的。

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

对于 DML 操作

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

1.6 五种 TM 表锁的含义：

ROW SHARE 行共享(RS)，允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁 ROW EXCLUSIVE 行排他(RX)，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁 SHARE 共享(S)，不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁 SHARE ROW EXCLUSIVE(SRX) 共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁 EXCLUSIVE (X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何排他锁。

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 无

1.7 加锁模式

第一种方式：自动加锁。

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

session1 scott: //用 for update 加锁

SQL> select * from dept where deptno>20 for update;

DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO 40 OPERATIONS BOSTON

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

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

session1 scott:

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

session2 sys: 跳过加锁的记录，锁定其他记录。

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

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

第二种方式：人工方式加锁，用 lock 命令以显式的方式加锁。

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

观察锁的动态视图 v$lock

观察锁的静态视图 dba_locks

select * from v$lock;

select * from dba_locks where session_id=149;

1.7 死锁和解锁

1.7.1 Oracle 自动侦测死锁，自动解决锁争用。

制作死锁案例：

scott:

SQL> select * from a;

ID_A ---------- 1 2

brain::

SQL> select * from b;

ID_B ---------- 100 200

ORA-00060: deadlock detected while waiting for resource

scott: //改自己,不提交

update table a set id=11 where id=1; brain: //改自己,不提交 update table b set id=1100 where id=100; scott: //改对方,被锁住 update table brain.b id=1000 where id=100; brain: //改对方,造成死锁 update table brain.b id=1000 where id=100;

1.7.2 管理员如何解鎖

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

SQL> select * from v$lock where type in ('TX','TM');

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

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

38B66D60 38B66D8C 127 TX 327680 1042 0 6

2985 0

00567BAC 00567BDC 134 TM 71090 0 3 0

2996 0

00567BAC 00567BDC 127 TM 71090 0 3 0

2985 0

37960894 379608D4 134 TX 327680 1042 6 0

2996 1

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;

SID SERIAL# SQL_TEXT -

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

127 2449 update emp1 set sal=8000 where empno=7788

SQL> select sid,serial#,blocking_session,username,event from v$session where blocking_session_status='VALID';

SID SERIAL# BLOCKING_SESSION USERNAME EVENT

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

127 2449 134 SCOTT enq: TX - row lock contention

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

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

更详细的信息，可以从多个视图得出，相关的视图有： v$session,v$process,v$sql,v$locked,v$sqlarea 等等

阻塞(排队）从 OEM 里看的更清楚 OEM-->Performance-->Additional Monitoring Links->Blocking Sessions(或 Instance Locks)