oracle 行级锁、表级锁、死锁、出现锁表甚至kill 操作无效的处理方式

一.遇到死锁，解锁的方法

问题：
  给oracle数据库插入海量数据时，发现归档日志剩余空间不足，所以停止执行，但是等待了很长时间，锁仍然没有被释放；

解决：

1.执行下面命令输出结果，其中 FSQL字段值 为解锁语句， PREV_HASH_VALUE 字段值可关联v$sql查出锁表语句

SELECT SESS.SID,  SESS.SERIAL#,  LO.ORACLE_USERNAME,  LO.OS_USER_NAME,  AO.OBJECT_NAME, LO.LOCKED_MODE,SESS.PREV_HASH_VALUE,
       'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ','||SESS.SERIAL#||'''' FSQL
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

select * from v$sql where hash_value =1201570115;

v$locked_object：有关锁信息的视图

SESSION_ID ：会话id
ORACLE_USERNAME ：产生锁的oracle用户
OS_USER_NAME：操作系统的名称

v$session：会话信息视图

sid ，SERIAL# ：这两个字段的值是用来杀进程的字段。
PREV_HASH_VALUE：通过这个字段关联v$sql可查出哪个sql语句引发的锁

v$sql：查看oracle执行语句的视图

2.解锁：

ALTER SYSTEM KILL SESSION '90,4213'

注：上述两个数字分别为v$session中SID，SERIAL# 字段的值

3.若执行解锁的操作失败，提示无法操作可执行：

select pro.spid from v$session ses,v$process pro where ses.sid=90 and ses.paddr=pro.addr; 

查出 spid 后再在linux中查看 相关进程的信息

ps -ef|grep  spid号

KILL -9命令杀掉这个进程即可

二.锁

2.1 锁概念

  数据库用来控制共享资源并发访问的机制；用于保护正在被修改的数据，直至提交或回滚了事务之后，其他用户才能更新数据

锁的优点：
一致性：保证一次只允许一个用户修改数据
完整性：为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改会反映给所有用户
并行性：允许多个用户访问同一数据；

2.2 锁的类型

2.2.1 行级锁

(1)TX

  行级锁是一种排他锁，防止其他事务修改此行，在使用 insert、update、delete、select for update时，oracle会自动应用行级锁，阻塞其他事务的操作；

查询表STU1的记录：

不同用户执行update操作：

在第二个用户 sql处于阻塞状态

查看数据库中的锁：

select * from V$LOCK;

注：
TM表示表级锁
TX表示行级锁

从上图可知，数据库对象的id为 74598，查看数据库对象id对应的对象：

select * from DBA_OBJECTS WHERE OBJECT_ID=74598;

(2) select for update

  允许用户每次选择多行记录进行更新，这些记录会被锁定，且只能由发起查询的用户进行编辑；
select * from 表名 for update，锁定表中所有行，不允许删改，允许插入 ；

语法：

SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT]

  OF子句用于指定即将更新的列，即锁定行上的特定列。
  WAIT子句指定等待其他用户释放锁的秒数，防止无限期的等待。

如：
在某个用户下执行select for update，锁定STU1中sid=3的行：

select * from STU1 where sid=3 for update ;

在另一个用户下设置等待5秒后返回：

select * from hr.STU1 where sid=3 for update wait 5;

在另一个用户下设置不等待直接返回：

select * from hr.STU1 where sid=3 for update nowait;

2.2.2 表级锁

使用命令显示地锁定表：

lock table 表名 in 模式 mode ;

表级锁有5种类型：注：(1) (2)通过行级锁实现表级锁

(1).行共享（ROW SHARE）：禁止其他用户使用排他锁；

A用户使用行共享锁:

lock table hr.STU1 in  row share mode;

B用户可添加除排他锁之外的锁:

lock table hr.STU1 in  row share mode;

执行update成功

回滚2个用户的锁

ROLLBACK ;

(2).行排他（ROW EXCLUSIVE）：禁止使用排他锁和共享锁；

(3).共享锁（SHARE）：锁定表，仅允许其他用户查询表中的行；禁止其他用户插入、删除、更新行；多个用户可以同时在同一个表上应用此锁；

(4).共享行排他(SHARE ROW EXCLUSIVE)：比共享锁更多的限制，禁止使用共享锁以及更高的锁；

(5).排他(EXCLUSIVE)：限制最强的表锁，仅允许其他用户查询此表的行，禁止修改和锁定表