最近工作中遇到数据库锁问题,为了了解问题的根本,重新复习了下数据库锁的概念
锁的产生
个人理解的大白话,当一个seesion在处理数据库表操作,另一个session也想同时进行操作,就有可能产生锁。下图更形象的表达了锁的概念。
Oracle中锁的两种级别
行级锁
行级锁是一种排他锁,防止其他事务修改此行,但是不会阻止读取此行的操作。在使用INSERT、UPDATE、DELETE 和SELECT…FOR UPDATE 等 语句时,Oracle 会自动应用行级锁锁定。SELECT…FOR UPDATE 语句允许用户每次选择多行记录进行更新,这些记录会被锁定,且只能由发起查询的用户进行编辑。只有在回滚或提交事务之后,锁定才会释放,其他用户才可以编辑这些记录。
模拟行级锁
第一个session开启事务更新一张表中的记录,不要提交事务
1 | update temp_itembom set quanlity=7 where id=347 |
第二个session更新同一张表中的同一条记录
1 | update temp_itembom set quanlity=8 where id=347 |
此时你会发现第二个session出现等待情况,如果第一个session事务提交或者回滚之后,第二个session才可以更新
也可以使用 ==select for update==锁定特定的行,可以使用WAIT 子句指定等待其他用户释放锁的秒数,防止无限期的等待。
使用FOR UPDATE WAIT 子句的优点如下:
⒈防止无限期地等待被锁定的行
⒉允许应用程序中对锁的等待时间进行更多的控制。
⒊对于交互式应用程序非常有用,因为这些用户不能等待不确定
例子:1
select * from temp_itembom where id=347 for update wait 5
上面代码是请求锁定一张表的一条记录,如果这条记录已经被其他事务锁定未释放,那么会等待5秒后退出,不会无限等待
表级锁
表级锁将保护表数据,在事务处理过程中,表级锁会限制对整个表的访问。可以使用LOCK TABLE 语句显示地锁定表。表级锁用来限制对表执行添加、更新和删除等修改操作。锁定表的语法如下:
1 | LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT]; |
表级锁的模式有以下几种:
- 行共享 (ROW SHARE, RS):
允许其他用户访问和锁定该表,但是禁止排他锁定整个表。行共享锁锁定后,在同一时刻,不同用户可以对同一个表中的被行共享锁锁定后的该行,具备增、删、改、查的功能。
- 行排他(ROW EXCLUSIVE,RX):
与行共享模式相同,同时禁止其他用户在此表上使用共享锁。使用SELECT…FOR UPDATE 语句会在表上自动应用排他锁。被行排他后,其他用户不能同时修改该行,但是可以插入行,可以查询该行,其他用户也不能再在该表上对此行进行排他。
- 共享(SHARE, S):
共享锁将锁定表,仅允许其他用户查询表中的行,但不允许插入、更新或删除行。多个用户可以同时在同一个表中放置共享锁,即允许资源共享,因此得名“共享锁”。例如,如果用户每天都需要在结帐时更新日销售表,则可以在更改该表时使用共享锁以确保数据的一致性。也就是说该表只能查,其他用户想修改表中行的数据,只需要对该表进行共享锁。
- 共享行排他(SHARE ROW EXCLUSIVE , SPX):
执行比共享表更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。共享行排他是除了该行以外的其他行也不能增、删、改。只能在此表中加低级表。要是想在该表中更改其他行的数据,就只有其他用户对该行进行共享行排他锁,也仅仅只能修改被这个用户锁定的行,而其他的行也修改不了。
- 排他(EXCLUSIVE,E):
对表执行最大限制。除了允许其他用户查询该表的记录,排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。这个锁应该叫锁中之王,他锁住了的话,其他用户就只有查询的功能了,就别想在该表中干别的事了。
这里只对共享(SHARE, S)做实例介绍
使用共享模式锁定一张表SQL
1 | lock table temp_itembom in share mode |
此时,在开启另外一个事务去更新这张表中的一条记录
1 | update temp_itembom set quanlity=8 where id=347; |
你会发现死锁产生了。在第一个事务释放锁之前,第二条更新语句会无限期等待指导第一条sql语句提交或者rollback.
如何解锁
知道产生锁原因,我们在锁出现后如何解锁,随着数据库业务的复杂和并发量的增多,我们不可能很容易的知道到底是哪张表,哪个session被锁住了
Oracle数据库提供下面表让我查询被锁住的,和解锁语句
解锁语句
==alter system kill session ‘sid,serial#’==
1.查询数据库中哪些对象正被锁住
1 | select session_id,oracle_username,process from v$locked_object |
1 | SESSION_ID ORACLE_USERNAME PROCESS OBJECT_ID |
Object_ID 86868的对象被锁住
查询具体对象信息
1 | select * from all_objects where object_id=86868 |
2.查询出现锁操作的session相关信息
1 | select sid,serial#,lockwait,status from v$session where sid in (40,49) |
1 | SID SERIAL# LOCKWAIT STATUS |
3.解锁语句
1 | alter system kill session '40,43481' |
国内查看评论需要代理~