共享锁与排他锁的基本原理：
基本原则
排他锁，排斥其他的排他锁和共享锁。
共享锁，排斥其他的排他锁，但不排斥其他的共享锁。
Oracle锁的种类
因为有事务才有锁的概念。Oracle数据库锁可以分为以下几大类：
DML锁（data locks，数据锁），用于保护数据的完整性。
DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义。
SYSTEM锁（internal locks and latches），保护数据库的内部结构。
我们探讨的是Oracle的DML操作（insert、update、delete），它包括两种锁：TX（行锁）和TM（表锁）。
TX 是面向事务的行锁，它表示你锁定了表中的一行或若干行。update和delete操作都会产生行锁，insert操作除外。
TM 是面向对象的表锁，它表示你锁定了系统中的一个对象，在锁定期间不允许其他人对这个对象做DDL操作。目的就是为了实施DDL保护。（理解一下参数ddl_lock_timeout）
比如一个update语句，有表级锁（即TM)和行锁（即TX锁）。Oracle是先申请表级锁TM（其中的RX锁）, 获得后系统再自动申请行锁(TX)。并将实际锁定的数据行的锁标志置位。
对于DML操作
行锁(TX)只有一种
表锁™共有五种，分别是 RS,RX,S,SRX,X。
五种TM表锁的含义：
1）ROW SHARE 行共享(RS)，
允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁
2）ROW EXCLUSIVE 行排他(RX)，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁
3）SHARE 共享(S)，不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁
4）SHARE ROW EXCLUSIVE(SRX) 共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁
5）EXCLUSIVE (X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何排他锁
案例1
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
session 1
SQL> update emp set deptno=30 where empno=7369;
1 row updated.
session 2
SQL> update emp set deptno=40 where empno=7499;
1 row updated.
session 1
SQL> update emp set deptno=50 where empno=7499;
update emp set deptno=50 where empno=7499
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
session 2
SQL> update emp set deptno=60 where empno=7369;
此查询等待锁
查询锁等待sql语句
SELECT
l.session_id sid,s.serial#,s.machine,s.terminal,
a.sql_id,a.sql_text,a.LAST_ACTIVE_TIME,
NVL(l.oracle_username, '(oracle)') AS username,
db.owner AS object_owner,db.object_name,
l.locked_mode,s.seconds_in_wait
FROM v$sqlarea a,v$session s,
v$locked_object l,dba_objects db
where s.prev_sql_addr = a.address
and l.session_id = s.sid
and db.object_id = l.object_id
and s.SECONDS_IN_WAIT > 5
order by sid,s.serial#;
alter system kill session '58,23';
至此，锁定时长大于5秒的sql被强制kill，dml操作回滚
案例 2
sql锁定语句如案例1
select a.inst_id,a.process,a.serial#,a.sql_id,a.event,a.status,a.program,a.machine,connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID || '@' || a.inst_id, ' <-') tree, level as tree_level from gv$session a start with a.blocking_session is not null
connect by (a.sid || '@' || a.inst_id) = prior (a.blocking_session || '@' || a.blocking_instance);
inst_id:会话所在的节点号
process：客户端进程号，与v$process中的spid不是同一个
sid，serial#,sql_id,status,program,machine:会话信息
lsleaf：是否为源头，0代表否，1代表是
tree：树形结构，锁的层次，例如：<-55@2<-55@2 <-62@1 从左到右依次表示为节点1的会话51被节点1的会话62阻塞，而节点2的会话55被节点1的会话62阻塞，所以节点1的会话62是锁源头
tree_level:树形层次
锁源头查杀方法：
select 'alter system kill session ''' || sid || '' || ',' || serial# || ',@' || inst_id || ''' immediate;' db_kill_session from
(select a.inst_id,a.process,a.sid,a.serial#,a.sql_id,a.event,a.status,a.program,a.machine,connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID || '@' || a.inst_id,' <- ') tree,level as tree_level from gv$session a
start with a.blocking_session is not null connect by (a.sid || '@' || a.inst_id) = prior
(a.blocking_session || '@' || a.blocking_instance)) where isleaf =1 order by tree_level asc;
select inst_id,'kill -9 ' || spid os_kill_session from (select p.inst_id,p.spid,a.sid,a.serial#,a.sql_id,a.event,a.status,a.program,a.machine,
connect_by_isleaf as isleaf,sys_connect_by_path(A.SID || '@' || a.inst_id, ' <- ') tree,level as tree_level from gv$session a,gv$process p where
a.inst_id = p.inst_id and a.paddr = p.addr start with a.blocking_session is not null connect by (a.sid || '@' || a.inst_id) = prior (
a.blocking_session || '@' || a.blocking_instance))
SELECT s.SID,
s.USERNAME,
s.OSUSER,
s.PROGRAM,
TO_CHAR(s.LOGON_TIME, 'yyyy-mm-dd hh24:mi:ss') AS LOGON_TIME,
TO_CHAR(t.START_DATE, 'yyyy-mm-dd hh24:mi:ss') AS START_DATE,
s.STATUS,
(SELECT q.SQL_TEXT FROM v$sql q WHERE q.LAST_ACTIVE_TIME = t.START_DATE AND rownum <= 10) AS SQL_TEXT
FROM v$session s,
v$transaction t
WHERE s.SADDR = t.SES_ADDR;
---oracle查看被锁的表和解锁的方法
select a.sql_text SQL语句,
b.etime 执行耗时,
c.user_id 用户ID,
c.SAMPLE_TIME 执行时间,
c.INSTANCE_NUMBER 实例数,
u.username 用户名,
a.sql_id SQL编号
from dba_hist_sqltext a,
(select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime
from dba_hist_sqlstat
where ELAPSED_TIME_DELTA / 1000000 >= 1) b,
dba_hist_active_sess_history c,
dba_users u
where a.sql_id = b.sql_id
and u.username = 'SCOTT'
and c.user_id = u.user_id
and b.sql_id = c.sql_id
--and a.sql_text like '%IN%'
order by SAMPLE_TIME desc, b.etime desc;
下面展示一些 内联代码片。
SELECT sess.SID, sess.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.LOCKED_MODE, ao.OBJECT_NAME
FROM V$LOCKED_OBJECT lo, v$session sess, dba_objects aoWHERE lo.SESSION_ID = sess.SID AND ao.OBJECT_ID = lo.OBJECT_ID;
USERNAME：死锁语句所用的数据库用户；
SID: session_id
LOCKWAIT：死锁的状态，如果有内容表示被死锁。
STATUS：状态，active表示被死锁
MACHINE：死锁语句所在的机器。
PROGRAM：产生死锁的语句主要来自哪个应用程序。
锁
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
--杀掉进程 sid,serial#
alter system kill session'210,11562';
--查询当前表空间各个表的数据量
Select Segment_Name,Sum(bytes)/1024/1024 neicun From User_Extents Group By Segment_Name
查看行锁信息
--查看数据库行锁
select se.SID,
o.object_name,
o.owner,
se.BLOCKING_SESSION,
se.sid,
se.serial#,
s.SQL_TEXT,
se.sql_id,
lo.LOCKED_MODE
from v$locked_object lo
left join dba_objects o
on lo.OBJECT_ID = o.object_id
left join v$session se
on lo.SESSION_ID = se.SID
left join v$sql s
on se.SQL_ID = s.SQL_ID
order by object_name, owner, blocking_session, se.sid, se.serial#;
--查询数据库正在运行的sql
select s.SID,
s.SERIAL#,
l.SQL_TEXT,
s.EVENT,
s.LAST_CALL_ET,
s.STATE,
s.WAIT_TIME,
s.BLOCKING_SESSION,
s.MACHINE,
s.SQL_ID,
s.USERNAME
from v$session s, v$sqlstats l
where s.STATUS = 'ACTIVE'
and s.TYPE = 'USER'
and s.sql_id = l.SQL_ID
order by last_call_et;
源头锁是blockIng_session为空的sid,serial#
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
