lock

最新推荐文章于 2019-01-17 10:06:00 发布

后端沉思录

最新推荐文章于 2019-01-17 10:06:00 发布

阅读量751

点赞数

分类专栏：【ORACLE/MYSQL】

本文链接：https://blog.csdn.net/BAT_os/article/details/45195267

版权

【ORACLE/MYSQL】专栏收录该内容

63 篇文章 0 订阅

订阅专栏

racle中锁的概念十分重要，对于其他关系型数据库更不用说，有的数据库发出select语句都要加锁，但是oracle有独立的undo（可从undo里读取修改过的数据）所以不加锁。
下面介绍oracle里的几种锁模式：

锁代码	锁模式名称	锁模式缩写	锁模式别名	锁级别
0	none	none	none
1	null	null	null	表级锁
2	ROW-S	SS	RS	表级锁
3	ROW-X	SX	RX	表级锁
4	Share	S	S	表级锁
5	S/ROW-X	SSX	SRX	表级锁
6	Exclusive	X	X	表/行级锁

锁代码说明：
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁 (row share)
3：Row-X 行专用(RX)：用于行的修改 (row exclusive)
4：Share 共享锁(S)：阻止其他DML操作(share)
5：S/Row-X 共享行专用(SRX)：阻止其他事务操作 (share row exclusive)
6：exclusive 专用(X)：排它锁，最高级锁，独立访问使用 (exclusive)
（数字越大锁级别越高, 影响的操作越多)
TX表示的是行级锁，TM表示的是表级锁。
下面是关于summary of table locks:

Sql Statement	Mode of table lock	RS	RX	S	SRX	X
select * from table ....	none	Y	Y	Y	Y	Y
insert into table ....	RX	Y	Y	N	N	N
update table ....	RX	Y*	Y*	N	N	N
delete from table ....	RX	Y*	Y*	N	N	N
select ... from table for update of	RS	Y*	Y*	Y*	Y*	N
lock table table in row share mode	RS	Y	Y	Y	Y	N
lock table table in row execlusive	RX	Y	Y	N	N	N
lock table table in share mode	S	Y	N	Y	N	N
lock table table in share row execlusive mode	SRX	Y	N	N	N	N
lock table table in execlusive mode	X	N	N	N	N	N

Y(YES) N(NO)
Y*:if no conficting row locks are held by another transaction.otherwise,waits occur.( 如果没有冲突行锁是被另一个事务持有。否则,等待出现)
对于锁的概念理解很重要，尤其在写应用程序的时候。
-bash-3.2$ lsb_release -a
Description: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> show user;
USER 为 "HR"
SQL> create table t(id number,name varchar2(10));
表已创建。
SQL> insert into t values(1,'diy');
已创建 1 行。
SQL> insert into t values(2,'os');
已创建 1 行。
SQL> COMMIT;
提交完成。

block（阻塞）：

SQL> update t set name='d' where id=1;

已更新 1 行。

SQL> select distinct sid from v$mystat;

SID
----------
21

在另一个session里：
SQL> select distinct sid from v$mystat;

SID
----------
19

SQL> update t set name='o' where id=1;

此时会一直等待，知道第一个事务结束（commit或rollback),这是block，不是死锁！

我们分析这个过程：

SQL> show user;
USER 为 "SYS"
SQL> select * from v$lock where sid in(19,21) order by sid;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
38687638 38687664 19 AE 100 0 4   0    810 0
38687ED4 38687F00 19 TX 262175 1888 0   6   230   0
004CA6B0 004CA6E0 19 TM 76714 0 3   0 230    0
004CA6B0 004CA6E0 21 TM 76714 0 3 0   240 0
37FBACC0 37FBAD00 21 TX 262175 1888   6 0   240     1（锁定了一个事务）
38687720 3868774C 21   AE 100 0   4 0   3730     0

已选择6行。

注意AE：Edition Lock，是11g新增加的锁类型，这是一个会话锁，只要有会话就会有一个锁。

此时session号为21的先更新id=1这一行，获得了一个TM（RX)锁，又获得了TX(X)锁；
session号为22的也获得了一个TM锁(RX)，但是和上面的TM兼容，所以此时没有阻塞，
但是由于行锁并不和上面的行锁兼容，所以没有获得行锁X,从上面的LMODE可以看出。

我们可以通过下面两个视图分析数据库中被锁的对象：
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
0 0 0 76714 19 HR oracle 6130 3
4 31 1888 76714 21 HR oracle 5022 3

SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID=76714;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- -------------- -------------- ------------------- ------- - - - ---------- ------------------------------
HR T 76714 76714 TABLE 22-4月 -15 22-4月 -15 2015-04-22:12:39:06 VALID N N N 1

我们在重建索引时，为了不影响系统性能，往往：
alter index index_name rebuild online;

但是我们为什么不：alter index index_name rebuild
下面简单操作示范：
SQL> create table ttt as select * from dba_objects;
表已创建。
SQL> select count(*) from dba_objects;

COUNT(*)
----------
72746
SQL> create index index_id on TTT(OBJECT_ID);

索引已创建。

SQL> set autotrace traceonly;
SQL> SELECT * FROM TTT;

已选择72746行。

执行计划
----------------------------------------------------------
Plan hash value: 774701505

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51569 | 10M| 283 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TTT | 51569 | 10M| 283 (1)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
308 recursive calls
0 db block gets
5909 consistent gets
1035 physical reads
0 redo size
8067725 bytes sent via SQL*Net to client
53755 bytes received via SQL*Net from client
4851 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed

SQL> alter index index_id rebuild;

索引已更改。

SQL> SELECT * FROM TTT;

已选择72746行。

执行计划
----------------------------------------------------------
Plan hash value: 774701505

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51569 | 10M| 283 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TTT | 51569 | 10M| 283 (1)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
36 recursive calls
0 db block gets
5886 consistent gets
0 physical reads
0 redo size
8067725 bytes sent via SQL*Net to client
53755 bytes received via SQL*Net from client
4851 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed

SQL> alter index index_id rebuild online;

索引已更改。

SQL> SELECT * FROM TTT;

已选择72746行。

执行计划
----------------------------------------------------------
Plan hash value: 774701505

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51569 | 10M| 283 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TTT | 51569 | 10M| 283 (1)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
5879 consistent gets
0 physical reads
0 redo size
8067725 bytes sent via SQL*Net to client
53755 bytes received via SQL*Net from client
4851 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72746 rows processed

两种扫描方式都是全表扫描，都会发生排序（sort操作）但是rebulid online操作比rebulid性能更好，从逻辑读次数可知。
rebulid操作会阻塞dml操作，而online操作不会（online操作降低了锁级别）
deadlock:
SQL>show user;
USER 为 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
21
SQL> update t set name='d' where id=1;
已更新 1 行。

SQL> SHOW USER;
USER 为 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
19
SQL> update t set name='s' where id=2;
已更新 1 行。

SQL> SHOW USER;
USER 为 "HR"
SQL> select distinct sid from v$mystat;
SID
----------
21
SQL> update t set name='y' where id=2;
update t set name='y' where id=2
*
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁

SQL> show user;
USER 为 "HR"
SQL> select distinct sid from v$mystat;
SID
----------
19
SQL> update t set name='s' where id=1;

上述顺序按操作顺序排列。
告警日志里（alert)有警告：
Wed Apr 22 14:43:05 2015
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_5024.trc.

查看转储文件：
.......
.......
*** 2015-04-22 14:43:04.053
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00030014-000009b7 24 21 X 32 19 X
TX-0006000d-00000a27 32 19 X 24 21 X

session 21: DID 0001-0018-00000019 session 19: DID 0001-0020-00000014
session 19: DID 0001-0020-00000014 session 21: DID 0001-0018-00000019

Rows waited on:
Session 21: obj - rowid = 00012BAA - AAASuqAAEAAABuvAAB

.........
.........
上面的内容是不是太详细了！哪个session，rowid都告诉我们了！可以更加深入研究死锁。