Server被锁的表乃至解锁

查看被锁表:

本文介绍通过Toad、EM及SQL语句来处理数据库产生的锁。在这之前需要对v$lock和v$session这两个数据字典有一定的了解。

MySQL的锁一般分为三种

select spId
  from master..SysProcesses
 where db_Name(dbID) = '数据库名称'
   and spId <> @@SpId
   and dbID <> 0

(一)使用Toad处理锁

  1. 行锁
  2. 表锁
  3. 页锁

解除锁:

(1)使用Toad的session browser查看锁情况

表锁

偏向MyISAM储存引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给设计的表加写锁。

对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的写请求,只有当读锁释放后,才会执行其他进程的写操作

exec ('Kill ' cast(@spid  as varchar))

图片 1

举例说明

首先创建一个MyISAM引擎的表

create table mylock(
    id int not null primary key auto_increment,
    name varchar(255)
)engine MyISAM;

session1加读锁

lock table mylock  read;

session1可以查看(SELECT),session2也可以查看
session1不可以查看其他表,不可以更改该锁表
session2可以查看锁表和其他表,更改锁表时会阻塞,session1解锁后执行
session2修改锁表

update mylock set name='a3' where id = 1;/*会阻塞*/

session1解锁,session2修改语句执行

unlock tables;

对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有写锁释放后,才会执行其他进程的读写操作
session1加写锁

lock table mylock  write;

session1不能查看其他表
session2查看锁表会阻塞,等session1解锁后执行

简而言之,就是读锁会阻塞写,但是不会阻塞读,而写锁会把读和写都阻塞

 

这里对每一个字段进行解释:

查看哪些表被加锁

show open tables;

也可以通过下面命令,检查Table_locks_waitedTable_locks_immediate状态量来分析系统上的表锁定

show status like 'table%';

Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在较高的表级锁争用情况
Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,魅力及获取锁值加1

此外,MyISAM的读写锁调度是写优先,这也是MyISAM不适合做写为主表的引擎,因为写锁以后,其他线程不能做任何操作,大量的更新会使查询难得到锁,从而造成永远阻塞

查看被锁表:

栏位名称 说明
SID session ID,每一个session都会产生一个sid,用于标识会话
User 产生锁的数据库用户
Lock Type

锁的类型,常见的有:

--DML锁

--Transaction锁(事物锁)等

Mode Held session保持锁的模式:
--none
--null(NULL)
--row-S(SS,行级共享锁。其它session只能查询这些数据行。SQL操作有select for update、lock for update、lock row share)
--row-X(SX,行级排它锁。在提交前不允许做DML操作。SQL操作有insert、update、delete、lock row share)
--share(S,共享锁。SQL操作有create index,lock share)

--S/Row-X(SSX,共享行级排它锁。SQL操作有lock share row exclusive)
--exclusive(X,排它锁。SQL操作有alter table、drop table、drop index、truncate table、lock exclusive等DDL操作)

Owner 被锁定的对象的属主
Object Type 被锁定的对象类型
Object Name 被锁定的对象名称
Blocking 该session是否正在阻塞其他session对资源进行访问。YES代表阻塞
Session Blocked 该会话是否正处于被阻塞的状态,打勾代表该session正在被其他session阻塞
OS User 建立该session的用户的OS名称
Machine Name 建立该session的用户的Machine名称

行锁

偏向InnoDB储存引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

 

InnoDB与MyISAM的最大不同有两点

  1. 支持事务(TRANSCTION)
  2. 采用行级锁

spid 锁表进程
tableName 被锁表名

(2)使用Toad解锁

并发事务带来的问题

  1. 更新丢失(A,B同时修改,其中一人的被覆盖)
  2. 脏读(B读到A修改但未提交的数据,若A事务回滚,则B读到的数据无效)
  3. 不可重复读(事务A读到事务B已经提交的数据)
  4. 幻读(B读到A新增但未提交的数据,若A事务回滚,则B读到的数据无效)

解锁:

图片 2

隔离级别

“脏读”,“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决
| 隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
| ------------- |:-------------| -----|
| 未提交读(Read Uncommitted) | 最低级别,只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交读(Read Committed) | 语句级 | 否 | 是 | 是 |
| 可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
| 可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |

数据库的事务隔离越严格,并发的副作用越小,但付出的代价就越大,因为事务实质上就是事务在一定程度上“串行化”进行,这杏眼与“并发”是矛盾的,同事,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据访问的能力。

查看隔离级别

show variables like 'tx_ioslation';
declare @spid  int 
Set @spid  = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill ' cast(@spid  as varchar)
exec(@sql)

 

举例说明

首先创建一个InnoDB引擎的表

create table linelock(
    id int(11),
    name varchar(255)
)engine InnoDB;

创建索引,根据索引锁定行

create index id_ind on linelock(id);
create index name_ind on linelock(name);

session1和session2把自动提交关闭

set autocommit = 0;

双方commit之前,session只能读自己所更改的,若autocommit=1则可立即读到其他session commit的数据

若同时修改同一行的数据,后修改的会被阻塞,等前者coommit后才执行,后者也要commit使改变生效,后者会覆盖前者

 

(二)使用SQL命令处理锁

索引失效导致行锁变成表锁

session1执行索引失效的语句

update linelcok set id = 10 where name= jack;/*varchar没加单引号使索引失效,行锁变表锁*/

session2执行其他行的更新语句会被阻塞,session1 commit之后执行

update linelcok set id = 9 where name= 'mike';/*会阻塞*/

(1)查看锁信息

间隙锁产生危害

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引加锁;对于键值在条件范围内但并不存在的记录叫做“间隙(GAP)”
InnoDB会对这个“间隙”加锁,这种锁的机制就是所谓的间隙锁(Next-Key Lock)
间隙锁会使不存在的键值被无辜地锁定,而造成在锁定时无法插入锁定键值范围内的任何数据。在某些场景下可能会对性能造成很大的危害

select 
  se.machine,
  se.sid,
  se.serial#,
  se.seconds_in_wait,
  se.paddr,
  lo.block
from 
  v$lock lo,
  v$session se
where
 lo.sid = se.sid
and
 lo.block > 0;    --bloc>0代表这个会话阻塞了其他会话

举例说明

id name
1 jack
3 mike
4 john

linelock表中数据

id name
1 jack
3 mike
4 john

session1执行范围更新

update linelock set name ='may' where id>1 and id <5;

session2执行间隙更新,要session1 commit之后才会执行

update linelock set name ='amy' where id=2;/*会阻塞*/

(2)查看哪个数据库对象被锁

查看行锁状态

show status like 'innodb_row_lock%';

各状态量说明如下:
Innodb_row_lock_current_waits:当前正在等待锁定的数量;
Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
Innodb_row_lock_time_avg:每次等待所花的平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;
Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

当等待次数很高,而且每次等待时长也不小的时候,我们需要分析系统为何有如此多的等待,然后制定优化计划。

select 
  lo.sid,
  do.owner,
  do.object_name
from 
  v$lock lo,
  dba_objects do
where
  lo.id1 = do.object_id
and
  lo.sid = 23;    --这里23是例子,我们需要根据上一步得到的sid来查看具体对象

优化建议

  1. 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
  2. 合理设计索引,尽量缩小锁的范围
  3. 尽可能较少检索条件,避免间隙锁
  4. 尽量控制事务大小,较少锁定资源量和时间长度
  5. 尽可能低级别事务隔离

(3)Kill Session

页锁

开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

alter system kill session 'sid,serial#';    --sid,serial从第1步中得到

(4)如果利用上面的命令杀死一个进程后,进程状态被置为"killed",但是锁定的资源很长时间没有被释放,那么可以在os一级再杀死相应的进程(线程),首先执行下面的语句获得进程(线程)号:

select 
  pr.spid,  --我们要的 
  se.osuser, 
  se.program
from 
  v$session se,
  v$process pr
where 
  se.paddr=pr.addr 
and 
  se.sid=24    --sid从第1步得到

(5)在OS级别Kill Process

(5.1) 在unix上,用root身份执行命令:

su - root 
#kill -9 spid    --即第步查询出的spid

(5.2)在windows(unix也适用)用orakill杀死线程,orakill是oracle提供的一个可执行命令,语法为:

orakill sid thread

其中:
sid:表示要杀死的进程属于的实例名 ,与上面的session id不同
thread:是要杀掉的线程号,即第4步查询出的spid

例:c:>orakill orcl 12345

 

(三)模拟锁的产生及处理

(1)对scott.emp表进行行更新,但是不提交

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
  …    …           …

14 rows selected

SQL> update scott.emp set job = 'SALESMAN' where empno = 7369;

1 row updated

 

(2)使用Toad查看锁情况,已经可以看到锁的存在

图片 3

 

(3)查看EM,从EM的top activity并不能看到锁的情况

图片 4

(4)查看V$lock。V$lock记录了当前数据库中存在的全部锁,锁是Oracle的一种正常的机制,但从这个视图并不能看出什么。对于用户而言,最关心的是TM和TX锁,结合上面Toad的结果,我们可以看到session id = 46的会话已经持续了454s。

SQL> select * from v$lock;

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
...              ...
000000008D45B1C0 000000008D45B218         53 AE          100          0          4          0        436          0
00007F2A9960F420 00007F2A9960F480         46 TM        73201          0          3          0        454          0
000000008C105C90 000000008C105D08         46 TX       458781       1379          6          0        454          0

对于TM锁(表级锁),ID1代表的是被锁定的object_id,ID2为0,通过dba_object可以查看到正在被锁的对象的名称。

SQL> select owner,object_name,object_id,object_type from dba_objects where object_id = 73201;

OWNER  OBJECT_NAME   OBJECT_ID OBJECT_TYPE
------ ------------ ---------- ------------
SCOTT  EMP               73201 TABLE

(5)再来查看v$session视图,SECOND_IN_WAIT字段代表会话处于等待的时间。

SQL> select se.SADDR,se.SID,se.SERIAL#,se.PADDR,se.USERNAME,se.MACHINE,se.SECONDS_IN_WAIT  from v$session se;

SADDR                   SID    SERIAL# PADDR            USERNAME                       MACHINE                     SECONDS_IN_WAIT 
---------------- ---------- ---------- ---------------- ------------------------------ --------------------------- --------------- 
000000008DF74F68         46         13 000000008DC9F4D0 LIJIAMAN                       WORKGROUPDESKTOP-TKAPD8E   1585 
000000008DF720F8         47        235 000000008DCA0510 DBSNMP                         localhost.localdomain       11 
...                     ...        ...

(6)在同一个session中执行delete操作,在新开的session中执行update操作。通过Toad,我们可以看到,在sid=46的session上,存在2个DML锁,值得一提的是,在我们对表emp进行操作时,由于其外键在dept表上,也将dept表锁住了。三个DML锁都是SX锁,即行级排它锁,46上还有一个x锁,即排它锁。

SQL> delete from scott.emp where emp.empno = 7369;
SQL> update scott.emp set emp.sal = sal   200 where emp.empno = 7369 ;

结果如图:

图片 5

 

由于执行了delete操作,将dept表也锁了起来

图片 6

 

(7)此时,再去观察EM,可看到大量的Application阻塞。并且可以看到这个阻塞是有sid=54的session引起的

图片 7

 

通过SQL ID查看具体执行的SQL语句

图片 8

 

(8)此时查看v$lock。从红色部分可以看到, session46与session54对object id

73201的对象产生了表级锁竞争,并且目前session46正在占用该表,导致该session阻塞了session54。

SQL> select * from v$lock;

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00007F2A9960C378 00007F2A9960C3D8         54 TM        73201          0          3          0       1510          0
00007F2A9960C378 00007F2A9960C3D8         46 TM        73201          0          3          0       3707          0
00007F2A9960C378 00007F2A9960C3D8         46 TM        73199          0          3          0       1798          0
000000008C105C90 000000008C105D08         46 TX       458781       1379          6          0       3707          1

(9)Kill Session

SQL> alter system kill session '46,13';

System altered

至此锁解除。

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Server被锁的表乃至解锁

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。