Oracle数据库锁的常用类型有哪些

Oracle数据库的锁类型

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁（data locks，数据锁），用于保护数据的完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。

DML锁的目的在于保证并发情况下的数据完整性，本文主要讨论DML锁。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

当Oracle执行DML语句时，系统自动在所要 *** 作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL *** 作产生不同类型的TM锁。如表1所示。

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

TX锁等待的分析

在介绍了有关地Oracle数据库锁的种类后，下面讨论如何有效地监控和解决锁等待现象，及在产生死锁时如何定位死锁的原因。

监控锁的相关视图 数据字典是Oracle数据库的重要组成部分，用户可以通过查询数据字典视图来获得数据库的信息。和锁相关的数据字典视图如表2所示。

TX锁等待的监控和解决在日常工作中，如果发现在执行某条SQL时数据库长时间没有响应，很可能是产生了TX锁等待的现象。为解决这个问题，首先应该找出持锁的事务，然后再进行相关的处理，如提交事务或强行中断事务。

死锁的监控和解决在数据库中，当两个或多个会话请求同一个资源时会产生死锁的现象。死锁的常见类型是行级锁死锁和页级锁死锁，Oracle数据库中一般使用行级锁。下面主要讨论行级锁的死锁现象。

当Oracle检测到死锁产生时，中断并回滚死锁相关语句的执行，报ORA-00060的错误并记录在数据库的日志文件alertSID.log中。同时在user_dump_dest下产生了一个跟踪文件，详细描述死锁的相关信息。

在日常工作中，如果发现在日志文件中记录了ora-00060的错误信息，则表明产生了死锁。这时需要找到对应的跟踪文件，根据跟踪文件的信息定位产生的原因。

如果查询结果表明，死锁是由于bitmap索引引起的，将IND_T_PRODUCT_HIS_STATE索引改为normal索引后，即可解决死锁的问题。

表1 Oracle的TM锁类型

锁模式 锁描述 解释 SQL *** 作

0 none

1 NULL 空 Select

2 SS(Row-S) 行级共享锁，其他对象只能查询这些数据行 Select for update、Lock for update、Lock row share

3 SX(Row-X) 行级排它锁，在提交前不允许做DML *** 作 Insert、Update、Delete、Lock row share

4 S(Share) 共享锁 Create index、Lock share

5 SSX(S/Row-X) 共享行级排它锁 Lock share row exclusive

6 X(Exclusive) 排它锁 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

表2 数据字典视图说明

视图名 描述 主要字段说明

v$session 查询会话的信息和锁的信息。 sid,serial#：表示会话信息。

program：表示会话的应用程序信息。

row_wait_obj#：表示等待的对象。

和dba_objects中的object_id相对应。

v$session_wait 查询等待的会话信息。 sid：表示持有锁的会话信息。

Seconds_in_wait：表示等待持续的时间信息

Event：表示会话等待的事件。

v$lock 列出系统中的所有的锁。 Sid：表示持有锁的会话信息。

Type：表示锁的类型。值包括TM和TX等。

ID1：表示锁的对象标识。

lmode,request：表示会话等待的锁模式的信

息。用数字0－6表示，和表1相对应。

dba_locks 对v$lock的格式化视图。 Session_id：和v$lock中的Sid对应。

Lock_type：和v$lock中的type对应。

Lock_ID1： 和v$lock中的ID1对应。

Mode_held,mode_requested：和v$lock中

的lmode,request相对应。

v$locked_object 只包含DML的锁信息，包括回滚段和会话信息。 Xidusn,xidslot,xidsqn：表示回滚段信息。和

v$transaction相关联。

Object_id：表示被锁对象标识。

Session_id：表示持有锁的会话信息。

Locked_mode：表示会话等待的锁模式的信

息，和v$lock中的lmode一致。

col owner for a12

col object_name for a16

select b.owner,b.object_name,l.session_id,l.locked_mode

from v$locked_object l, dba_objects b

where b.object_id=l.object_id

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time

如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：

alter system kill session 'sid,serial# '

如果出现了锁的问题, 某个DML *** 作可能等待很久没有反应。

当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题

Oracle锁表(死锁) 2011-05-03 17:46:41| 分类： Java技术 | 标签： |字号大中小 订阅 .

数据库与 *** 作系统一样，是一个多用户使用的共享资源。 当多个用户并发地存取数据时，在数据库中就会发生多个事务同时存取同一数据地情况。 若对并发 *** 作不加控制就可能会读取和存储不正确地数据，破坏数据库地一致性。 加锁时实现数据库并发控制地一个非常重要地技术。 在实际应用中经常会遇到地与锁相关地异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严重影响应用的正常执行。

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（即S锁）。当数据对象被加上排它锁时，其他的事务不能不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

死锁的第一种情况：

一个用户A访问表A（锁住了表A），然后又访问表B； 另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁产生了。

解决方法：

这种死锁比较常见，是由于程序的BUG产生的，除了调整程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表 *** 作时，尽量按照同样的顺序进行处理，尽量避免同时锁定两个资源，如 *** 作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

死锁的第二种情况

用户A查询一条记录，然后修改该条记录；这时用户B修改该条记录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在必须等A释放掉共享锁，而A由于B的独占锁而无法上升到独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目种经常发生，如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次 *** 作，很容易就出现这种死锁的情况。

解决方法：

1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录 *** 作。

2、使用乐观锁进行控制。乐观锁大多是基于数据版本（version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库增加一个“version”字段来实现。读取处数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交的数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B *** 作过程中，都没有对数据库加锁），大大提升了大并发量下的系统整体性表现。 Hibernate在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是我们的系统中实现，来自外部系统的用户更新 *** 作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的select.......for update语句，以保证 *** 作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个 *** 作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户帐户余额），如果采用悲观锁机制，也就意味整个 *** 作过程中（从 *** 作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括 *** 作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的结果。所以，采用悲观锁进行控制时一定要考虑清楚。

死锁的第三种情况

如果在事务种执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行之后，就很容易发生死锁和阻塞。类似的情况还有当表种的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行 分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

***查询死锁表以及解锁表***

通过select * from v$locked_object

可以获得被锁的对象的object_id及产生锁的会话sid，通过查询结果中的object_id，可以查询到具体被锁的对象。

锁有以下几种模式:

0：none

1：null 空

2：Row-S 行共享(RS / S锁)：共享表锁

3：Row-X 行专用(RX / X锁)：用于行的修改

4：Share 共享锁(S)：阻止其他DML *** 作

5：S/Row-X 共享行专用(SRX)：阻止其他事务 *** 作

6：exclusive 专用(X)：独立访问使用

数字越大锁级别越高, 影响的 *** 作越多。

一般的查询语句如select ... from ... 是小于2的锁, 有时会在v$locked_object出现。

select ... from ... for update是2的锁。

当对话使用for update子串打开一个游标时，

所有返回集中的数据行都将处于行级(Row-X)独占式锁定，

其他对象只能查询这些数据行，不能进行update、delete或select...for update *** 作。

insert / update / delete ... 是3的锁。

没有commit之前插入同样的一条记录会没有反应,

因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。

创建索引的时候也会产生3,4级别的锁。

locked_mode为2,3,4不影响DML(insert,delete,update,select) *** 作,

但DDL(alter,drop等) *** 作会提示ora-00054错误。

有主外键约束时 update / delete ... 可能会产生4,5的锁。

DDL语句时是6的锁。

以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：

select object_id,session_id,locked_mode from v$locked_object

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time

如果有长期出现的一列，可能是没有释放的锁。

我们可以用下面SQL语句杀掉长期没有释放非正常的锁：

MySQL 中有哪些锁？

数据库中锁的设计初衷处理并发问题，作为多用户共享资源，当出现并发访问的时候，数据库需要合理控制资源访问规则。锁就是实现这些访问规则中的重要数据。

锁的分类

根据加锁范围，MySQL 里面的锁可以分成 全局锁 、 表级锁 、 行锁 三类。

全局锁

全局锁，就是对整个数据库实例加锁，MySQL 提供了一个加全局读锁的方法，命令是:

Flush tables with read lock (FTWRL)

当需要整个库只读状态的时候，可以使用这个命令，之后其他线程的：数据更新语句（增删改），数据定义语句（建表，修改表结构）和更新事务的提交语句将会被阻塞。

全局锁的使用场景

全局锁的定型使用场景，做 全库逻辑备份 。也就是把整个库每个表都 Select 出来，然后存成文本。

如何整个库都只读，会有什么问题？ 如果你在主库上备份，那么在备份期间都不能执行更想，业务就基本上停摆。 如果在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog ,会导致从延迟。 既然要全库只读， 为什么不使用set global readonly=true的方式呢?

readonly 方式也可以让全库进入只读状态,但我还是会建议你用FTWRL方式， 主要有两个原因：

一是， 在有些系统中， readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大， 我不建议你使用。 二是， 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开， 那么MySQL会自动释放这个全局锁， 整个库回到可以正常更新的状态。而将整个库设置为readonly之后， 如果客户端发生异常， 则数据库就会一直保持readonly状态， 这样会导致整个库长时间处于不可写状态， 风险较高 表级别锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lok, MDL）。表锁的语法是 ：

lock tables ... read/write

与 FTWRL 类似,可以使用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意的是，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的 *** 作对象。

MDL 表级锁

MDL 不需要显示使用，在访问一个表的时候自动加上， MDL 保证读写的正确性，也就是说在查询数据时，不允许有其他线程对这个表结构做变更。

什么 *** 作会加 MDL 锁？

在MySQL 5.5版本中引入了MDL， 当对一个表做增删改查 *** 作的时候，加 MDL读锁 ；当要对表做结构变更 *** 作的时候，加 MDL写锁 。

读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。 读写之间、写锁之间是互斥的，用来保证变更表结构 *** 作的安全性，如果有两个线程要同时给一个表加字段，其中一个要等另外一个执行完才能执行。 更改表结构要注意哪些？

给一个表加字段， 或者修改字段， 或者加索引， 需要扫描全表的数据。在对大表 *** 作的时候， 你肯定会特别小心， 以免对线上服务造成影响。而实际上， 即使是小表， *** 作不慎也会出问题，导致整个库的线程爆满。

举个例子

我们来看一下下面的 *** 作序列， 假设表t是一个小表。

image

session A先启动， 这时候会对表t加一个 MDL读锁 。由于session B需要的也是 MDL读锁 ， 因此可以正常执行。 session C会被blocked， 是因为session A的MDL读锁还没有释放， 而session C需要MDL写锁， 因此只能被阻塞，读写锁互斥。 如果只有session C自己被阻塞还没什么关系， 但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查 *** 作都需要先申请MDL读锁， 就都被锁住， 等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁， 而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话， 这个 库的线程很快就会爆满 。事务中的MDL锁， 在语句执行开始时申请， 但是语句结束后并不会马上释放， 而会等到整个事务提交后再释放。

怎么解决这个 更改表结构问题

比较理想的机制是， 在alter table语句里面设定等待时间， 如果在这个指定的等待时间里面能够拿到MDL写锁最好， 拿不到也不要阻塞后面的业务语句， 先放弃。

ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

MyISAM和InnoDB存储引擎使用的锁：

封锁粒度小：

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁（因为意向锁是表锁）其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下所示

参考

参考

行锁的三种算法：

这条语句阻止其他事务插入10和20之间的数字，无论这个数字是否存在。 间隙可以跨越0个，单个或多个索引值。

https://blog.csdn.net/u014316026/article/details/78726459

共享锁：

排他锁：

https://www.cnblogs.com/mysql-hang/articles/11027685.html

乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改（天真）， *** 作数据时不会上锁 ，但是 更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试 ；适用于读多写少的场景。

乐观锁的实现方式 有：

关闭自动提交后，我们需要手动开启事务。

上述就实现了悲观锁，悲观锁就是悲观主义者，它会认为我们在事务A中 *** 作数据1的时候，一定会有事务B来修改数据1,所以，在第2步我们将数据查询出来后直接加上排它锁（X）锁，防止别的事务来修改事务1，直到我们commit后，才释放了排它锁。

---