Oracle 锁
1锁
锁 lock是用于对共享资源的并发访问，还能提供数据的完整性跟一致性，多个用户访问和修改数据或数据结构，就要有一种机制来防止对同一份信心的并发修改，
Oracle：事务是数据库的核心
应该延时要适当时机才提交，必要时才提交，事务的大小只应该有业务逻辑来决定
如果需要，就长时间的保持对数据所加的锁，
在oracle中，行级锁没有相关的开销，固定的常量
不会对锁升级
同时得到并发性和一致性，非阻塞读，写不会阻塞读，读不会被写阻塞
2 锁定问题
2.1 防止丢失更新
两个会话操作同一行
2.2 悲观锁定:必须在有状态或有连接环境，粒度较大，代价昂贵
Select* from emp for update nowait/for update wait n
一个会话修改在没提交之前别的会话看不见所修改。
会话1：select * from emp where empno=7934 and ename='MILLER' and sal=1300
for update nowait
会话2：select * from emp where empno=7934 正常
会话2：update emp set mgr=7781 where empno=7934 挂起
会话1：commit或者rollback；会话2就会更新；
在会话1查看该数据并没有修改；
会话2 commit或者rollback，其他会话才能看见其修改。
所有表都应该有1个主键（select最多获取一条数据，因为条件包含主键）而且主键是不可变的，不应该更新主键.
2.2 乐观锁定
把所有锁定都延迟到提交之前才去做，我们认为数据不会被其他用户修改，会等到最后一刻才去验证
乐观控制的方法：
1使用版本列的乐观锁定：对要保护的表新增加一个number或timestamp列，通常通过表上的一个行触发器来维护，这个触发器负责递增number列或更新timestamp列
create table dept_lock ( deptno number(2), dname varchar2(14), loc varchar2(13), last_mod timestamp with time zone default systimestamp not null, constraint dept_lock_pk primary key(deptno) ) insert into dept_lock( deptno, dname, loc ) select deptno, dname, loc from scott.dept; select dname, loc, to_char( last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' ) from dept_lock where deptno = 10; update dept_lock set dname = initcap(dname), last_mod = systimestamp where deptno =10 and last_mod = to_timestamp_tz('29-MAY-2013 03.40.47.500000 PM +08:00', 'DD-MON-YYYY HH.MI.SSXFF AM TZR' );
第一次更新1行，第二次在执行就不会更新，因为条件last_mod 不满足
还可以使用trigger来维护这个last_mod字段，建议避免使用触发器，让dml来负责，触发器会引入大量开销
2 使用校验和的乐观锁定：用基数生成一个虚拟列 ora_hash
select deptno, dname, loc, ora_hash( dname || '/' || loc ) hash
from dept
where deptno = 10;
10 ACCOUNTING NEW YORK 401273349
然后更新
update dept
set dname = initcap(dname)
where deptno = 10
and ora_hash( dname || '/' || loc ) = 401273349
第一次成功，第二次hash值重新计算，再次更新则失败
2.3 阻塞
如果某个会话持有某资源的锁，而另一个会话在请求这个资源，就会出现阻塞locking
2.3.1 阻塞的insert
一个带主键的表，或者表上有唯一约束，但2个会话视图用相同的值插入一行，其中一个会阻塞，直到另一个会话提交或回滚，如果提交，则另个会话报错，回滚，阻塞的会话就会成功。
2.3.2 阻塞的update，delete，merge
如果代码中存在视图更新其他人正在更新的行（有人已经锁住了这一行），可以通过使用select for update nowait，
2.4死锁
有2个会话都在互相请求另一个会话持有的资源，就会死锁
A,b 2个表，各一行数据
A b
Update a update b
Update a（阻塞）
Update b（死锁）
Session1
SQL> create table a (x number);
Table created.
SQL> create table b (y number);
Table created
SQL> insert into a values (1);
1 row created.
SQL> insert into b values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> update a set x=x+1;
Session2
SQL> update b set y=y+1;
1 row updated.
SQL> update a set x=5;-----该会话被阻塞
Session1
SQL> update b set y=8;----阻塞
1 row updated.
Session2
update a set x=5------发现死锁
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
SQL> commit;-----提交或回滚
Commit complete.
Session1
SQL> select * from a;
X
----------
2
SQL> select * from b;
Y
----------
8
SQL> commit;
Session2
SQL> select * from a;
X
----------
1
SQL> select * from b;
Y
----------
3
SQL> select * from a;-------当session 1 提交过后
X
----------
2
SQL> select * from b;
Y
----------
8
---会话b阻塞
select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid;
Oracle会回滚与死锁有关的某条语句，会话b必须决定将b表上未执行的工作提交还是回滚
Oracle认为死锁很少出现，每次出现都会在服务器上创建一个跟踪文件
Oracle会在以下情况，修改父表后会对子表加一个全表锁
1 如果更新了父表的主键,若外键没有索引，子表会被锁住
2 如果删除了父表中的一行，整个子表会被锁住（没有index）
3 如果合并到父表，整个子表会被锁住（么有index） 11g后取消
Session1
SQL> create table a1 (x int primary key);
Table created.
SQL> create table a2 (x references a1);
Table created.
SQL> insert into a1 values(1);
1 row created.
SQL> insert into a1 values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into a2 values(2);
1 row created.
Session2
SQL> delete from a1 where x=1;-----卡住
Session1 提交
select * from v$session;
Oracle在修改父表后会对子表加一个全表锁（外键未加索引）
查看为建索引的外键
select table_name, constraint_name, cname1 || nvl2(cname2,','||cname2,null) || nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) || nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) || nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) columns from ( select b.table_name, b.constraint_name, max(decode( position, 1, column_name, null )) cname1, max(decode( position, 2, column_name, null )) cname2, max(decode( position, 3, column_name, null )) cname3, max(decode( position, 4, column_name, null )) cname4, max(decode( position, 5, column_name, null )) cname5, max(decode( position, 6, column_name, null )) cname6, max(decode( position, 7, column_name, null )) cname7, max(decode( position, 8, column_name, null )) cname8, count(*) col_cnt from (select substr(table_name,1,30) table_name, substr(constraint_name,1,30) constraint_name, substr(column_name,1,30) column_name, position from user_cons_columns ) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' group by b.table_name, b.constraint_name ) cons where col_cnt > ALL ( select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 ) and i.column_position <= cons.col_cnt group by i.index_name )
EMP FK_DEPTNO DEPTNO
表emp上的deptno列 外键
未在外键上建立索引还可能带来的问题
如果有on delete cascade，而且没有对子表建立索引，删除父表的每一行行都会对子表做全表扫描，
从父表查询子表，速度慢，2个相关联
create index GOODS_LANGUAGE_inex on GOODS_LANGUAGE(GOODS_ID)
oracle从来不会进行锁升级
3 锁类型
Dml锁：行级锁，表级锁
Ddl锁：create或alter等语句，ddl锁保护对象结构
Latch
3.1 dml锁
Dml锁用于确保一次只有一个人修改某一行，当你在处理这行的时候，其他人不能删除该表
1 tx锁
TX锁，事务发起第一修改时会得到TX锁（事务锁），而且会一直持有这个锁，直至事务提交或回滚，
TX锁用作一种排队机制，使得其他会话可以等待这个事务的执行，
V$transaction:对应每个活动事务都包含一个条目
V$session：显示已登录的会话
V$lock：对应持有所有sequence队列锁以及正在等待锁的会话，
create table dept as select * from scott.dept; create table emp as select * from scott.emp; alter table dept add constraint dept_pk primary key(deptno); alter table emp add constraint emp_pk primary key(empno); alter table emp add constraint emp_fk_dept foreign key (deptno) references dept(deptno); create index emp_deptno_idx on emp(deptno); update dept set dname = initcap(dname);
select username, v$lock.sid, trunc(id1/power(2,16)) rbs, bitand(id1,to_number('ffff','xxxx'))+0 slot, id2 seq, lmode, request from v$lock, v$session where v$lock.type = 'TX' and v$lock.sid = v$session.sid and v$session.username = USER;
prompt update emp set ename = upper(ename);;
prompt update dept set deptno = deptno-10;;
SCOTT 133 7 23 564 0 6
SCOTT 133 3 2 651 6 0
SCOTT 143 7 23 564 6 0
Lmode为6是一个排他锁，request为0 你拥有这个锁，request为6就表示该会话正在请求锁，
select XIDUSN, XIDSLOT, XIDSQN
from v$transaction;
查看阻塞的SQL
select (select username from v$session where sid=a.sid) blocker, a.sid, ' is blocking ', (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
SCOTT 143 is blocking SCOTT 133
Tm锁
3.2 TM锁（表级锁）用于在修改表数据时，表的结构不能被改变
例如已经更新了一个表，会得到这个表的tm锁，这会防止另一个用户在该表上执行drop或alter命令，如果有表的一个tm锁，另一个用户试图在该表上执行ddl，得到错误 ora--00054
每个事务只能得到一个tx锁，但tm锁则不同，修改了多少个对象，就能得到多少个tm锁
create table t1 ( x int );
create table t2 ( x int );
connect /
insert into t1 values ( 1 );
insert into t2 values ( 1 );
select (select username from v$session where sid = v$lock.sid) username, sid, id1, id2, lmode, request, block, v$lock.type from v$lock where sid = (select sid from v$mystat where rownum=1);
SCOTT 143 52953 0 3 0 0 TM 多个tm锁
SCOTT 143 52954 0 3 0 0 TM
SCOTT 143 327699 644 6 0 0 TX
select object_name, object_id
from user_objects
where object_name in ('TT1','TT2')
TT1 52953
TT2 52954
3.2 ddl锁
Ddl操作中会自动为对象加ddl锁（ddl lock），从而保护对象不被其他会话所修改
如果在表t上执行alter table t，表t上就会添加一个排他DDL锁，防止其他会话得到这个表的ddl锁和tm锁
Ddl操作期间，会一直持有ddl锁
3种ddl类型
排他ddl锁：期间可以查询该表，无法以任何方式修改该表，防止其他会话得到他们自己的ddl锁或tm锁
共享ddl锁：该锁会保护引用对象的结构，使之不会被其他会话修改，但是允许修改数据（对依赖的对象加个共享ddl锁）
可中断解析锁：这些锁允许一个对象向另外某个对象注册其依赖性
大多数的ddl都带有一个排他的ddl锁：
alter table t move
期间，表t不能被别人修改，可以使用select查询，
在oracle中，现在有些ddl操作没有ddl锁也可以发生
Create index t_idx on t（x） online；
Online关键字会改变具体建立索引的方式，oracle并不是加一个排他ddl锁来防止数据修改，会试图得到表上的一个低级（mode 2）的tm锁，会有效的防止其他的ddl发生，同时还允许dml正常进行
对表完成直接路径加载和创建索引不能同时进行
Od锁是11g新增加，支持真正的联机ddl
另外一类ddl会获得共享ddl锁，在创建存储的编译对象（过程跟视图）时，会对依赖的对象加这种共享ddl锁。
Create view myview
As
Select emp.empno,emp.ename,dept.deptno,dept.dname
From emp,dept
Where emp.deptno=dept.deptno;
表emp跟dept都会加上共享ddl锁，而create view命令仍在处理，可以修改这些表的内容，但是不能修改其结构
Ddl 可中断解析锁，你的会话解析一条语句时，对于该语句引用的每一个对象都会加一个解析锁，加锁的目的是：如果以某种方式删除或修改了一个被引用的对象，可以将共享池中已解析的缓存语句置为无效（刷新输出）
视图dba_ddl_locks
，默认没有安装，运行/rdbms/admin/catblock.sql脚本，（sys运行）
可以得到我的会话锁定的所有对象
Alter procedure p compile
Select session_id,owner,name,type,mode_held,mode_requested
From dba_ddl_locks
Where session_id =(select sid from v$mystat where rownum=1)
这个视图对开发人员很有用，发现测试或开发系统中某段代码无法编译时，将会挂起并最终超时，说明有人正在使用这段代码，可以使用这个视图来找他使用的人
Oracle11g r2版本引入一个新特性，基于版本的重定义(ebr),允许在同一个模式中同时有同一个存储过程的多个版本
oracle 锁 ORACLE里锁有以下几种模式:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive
数字越大锁级别越高, 影响的操作越多。
同一个用户，不同用户的区别
1级锁有：Select，有时会在v$locked_object出现。
2 Row-S 行共享(RS)级锁有：Select for update,Lock For Update,Lock table tt in Row Share mode
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3 Row-X 行独占(RX)级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4 Share 共享锁(S)：级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5 S/Row-X 共享行独占(SRX)级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
6 exclusive 独占(X)级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
一个session，Inset后不commit，locked_mode=3,然后create index，自动提交，锁消失，drop可以
Select* for update，locked_mode=3 --10G已经把FOR UPDATE改成了表级3号锁
查看锁表进程SQL语句 1
select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid;
查看锁表进程SQL语句2：
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
杀掉锁表进程：
如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK
alter system kill session '738,1429';
select 'alter system kill session '''||trim(t2.sid)||','||trim(t2.serial#)||''';' from v$locked_object t1, v$session t2 where t1.session_id=t2.sid;
SELECT l.session_id sid, s.serial#, l.locked_mode 锁模式, l.oracle_username 登录用户, l.os_user_name 登录机器用户名, s.machine 机器名, s.terminal 终端用户名, o.object_name 被锁对象名, s.logon_time 登录数据库时间 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#;
select l.session_id,s.serial#,l.locked_mode,l.oracle_username, l.os_user_name,s.machine,s.terminal,o.object_name,s.logon_time from v$locked_object l,all_objects o,v$session s where l.object_id=o.object_id and l.session_id=s.sid order by 1,2;
查看数据库中被锁住的对象和相关会话
SELECT a.sid, a.serial#, a.username, a.program, c.owner, c.object_name FROM v$session a, v$locked_object b, all_objects c WHERE a.sid = b.session_id AND c.object_id = b.object_id;
--查看当前所有的锁
select (select username from v$session where sid = v$lock.sid) username, sid, trunc((case ----如果锁类型是TX,则显示事务 ID,对应v$transaction.XIDUSN，如果是 TM锁，则对应 user_objects 里的 object_id when type = 'TX' then trunc(ID1/power(2, 16)) else id1 end)) T#_or_obj#, trunc((case ----如果锁类型是TX，则显示事务 SLOT,对应 v$transaction.XIDSLOT when type = 'TX' then trunc(bitand(ID1, to_number('ffff', 'xxxx')) + 0) else 0 end)) slot, id2 seq,---这里对应v$transaction.XIDSQN lmode,request,block, type from v$lock where type ='TX’'or type='TM' order by sid;
首先，如果系统有锁，我们需要看锁对应的类型和锁定的对象，有的是行级排它锁，表级共
享锁，这些概念，应该查资料去搞清楚，上面的 SQL 是查，锁定的对象用的，以及事务的
---就是查锁阻塞
----增加了serial#
select c.username,a.sid,c.serial#,' is blocking ',d.username,b.sid,d.serial# from (select sid,id1,id2 from v$lock where block =1) a, (select sid,id1,id2 from v$lock where request > 0) b, (select sid,serial#,username from v$session ) c, (select sid,serial#,username from v$session ) d where a.id1=b.id1 and a.id2=b.id2 and a.sid=c.sid and b.sid=d.sid;
