Oracle、SQLServer中如何锁定DB和解锁DB

今天有同事问，很多人同时更新操作DB，如何防止数据冲突。编制了一份文件。虽然不是原创，但是对理解数据库锁定和DB解锁是有帮助的。

1.为什么要引入锁？

多个用户同时对数据库进行并发操作会带来以下数据不一致问题:

丢失更新

a、B两个用户读取相同的数据并修改，一个用户的修改结果破坏了另一个用户的修改结果，比如订票系统。

黄色读物

用户A修改了数据，然后用户B再次读取数据。但是，用户A出于某种原因取消了对数据的修改，数据被恢复到其原始值。此时，用户B获得的数据与数据库中的数据不一致。

不可靠的阅读

a用户读取数据，然后B用户读取数据并修改它。此时，当用户再次读取数据时，发现两个值不一致。

并发控制的主要方法是加锁，即在一段时间内禁止用户做某些操作，以避免数据不一致。

锁的分类

有两种类型的锁:

1.从数据库系统的角度来看:可以分为独占锁(exclusive lock)、共享锁和更新锁。

MS-SQL Server使用下列资源锁定模式。

锁定模式描述

共享用于不更改或更新数据的操作(只读操作)，如SELECT语句。

Update (U)用于可再生资源。当多个会话正在读取、锁定和可能更新资源时，防止常见形式的死锁。

行(x)用于数据修改操作，如插入、更新或删除。确保不会同时对同一资源进行多次更新。

有意锁用于建立锁的层次结构。意向性锁的类型有:意向性共享(IS)、意向性独占(IX)、意向性独占共享(SIX)。

当执行依赖于表模式的操作时，使用模式锁。锁的类型是模式修改(Sch-M)和模式稳定性(Sch-S)。

将数据大容量复制到表中并指定TABLOCK提示时，使用大容量更新(BU)。

共享锁

共享锁允许并发事务读取(选择)资源。当资源上有一个共享(S)锁时，任何其他事务都不能修改数据。一旦读取了数据，资源上的共享(S)锁将被立即释放，除非事务隔离级别被设置为可重复或更高，或者共享(S)锁将在事务的生命周期内使用锁提示来保持。

更新锁

Update (u) lock可以防止常见形式的死锁。常规更新模式由一个事务组成，该事务读取记录，获取资源(页或行)的共享(S)锁，然后修改行。此操作需要将锁转换为独占(X)锁。如果两个事务获取一个资源的共享模式锁，然后同时尝试更新数据，则一个事务会尝试将该锁转换为独占(x)锁。从共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生了锁定等待。第二个事务试图获取用于更新的排他锁(x锁)。死锁的发生是因为两个事务都被转换为独占(x)锁，并且每个事务都在等待另一个事务释放共享模式锁。

为了避免这种潜在的死锁问题，请使用update (U)锁。一次只有一个事务可以获取资源的更新(U)锁。如果一个事务修改了一个资源，那么update (U)锁就被转换成一个exclusive (X)锁。否则，该锁将被转换为共享锁。

互锁

排他锁(x锁)可以防止并发事务访问资源。其他事务不能读取或修改由排他锁(x锁)锁定的数据。

意向锁

有意锁意味着SQL Server需要获得层次结构中某些底层资源的共享(S)锁或排他(X)锁。例如，放置在表级别的共享有意锁表示事务打算在表中的页面或行上放置共享(S)锁。在表级别设置有意锁可以防止另一个事务随后获取包含该页的表上的独占(x)锁。有意锁可以提高性能，因为SQL Server只在表级别检查有意锁，以确定事务是否可以安全地获取该表上的锁。不需要检查表中每一行或每一页的锁来确定事务是否可以锁定整个表。

意向性锁包括意向性共享(IS)、意向性独占(IX)和意向性独占共享(SIX)。

锁定模式描述

意图(IS)是指事务的意图是通过在每个资源上放置S锁来读取层次结构中的一些(但不是全部)底层资源。

故意排除(IX)通过在每个资源上放置X个锁，表明事务的意图是修改层次结构中的一些(但不是全部)底层资源。是IX is的超集。

有意图的独占共享(六)通过在每个资源上放置IX锁，表明事务的意图是读取层次结构中的所有底层资源，并修改部分(但不是全部)底层资源。允许顶层资源上的并发IS锁。例如，一个表的六个锁在表上放置一个六锁(允许并发的IS锁)，在当前修改的页上放置一个IX锁(在修改的行上放置一个X锁)。虽然每个资源在一段时间内只能有一个SIX锁，以防止其他事务更新该资源，但其他事务可以通过在表级别获取IS锁来读取层次结构中的底层资源。

独占锁(Exclusive lock):只有加锁的程序才能使用它，不接受对它的任何其他操作。执行数据更新命令时，SQL Server将自动使用排他锁。当对象上有其他锁时，不能向其添加独占锁。

共享锁:被共享锁锁定的资源可以被其他用户读取，但是其他用户不能修改。执行Select时，SQL Server将向对象添加共享锁。

更新锁:当SQL Server准备更新数据时，首先用更新锁锁定数据对象，这样数据就不能被修改，但可以被读取。当SQL Server决定更新数据时，它会自动将更新锁更改为独占锁。当对象上有其他锁时，不能添加该对象。

2.从程序员的角度来说:分为乐观锁和悲观锁。

乐观锁:完全依赖数据库来管理锁。

悲观锁:程序员自己管理数据或对象上的锁处理。

MS-SQLSERVER使用锁来实现同时在数据库中执行修改的多个用户之间的悲观并发控制
三个锁的粒度。

锁粒度是被阻塞目标的大小。如果锁粒度小，那么并发性会高，但是开销也会高。如果锁粒度很大，则并发性会很低，但开销也会很低。

SQL Server支持的锁的粒度可以分为获取行、页、键、键范围、索引、表或数据库的锁。

资源描述

行标识符。用于单独锁定表中的一行。

钥匙中的行锁。用于保护可序列化事务中的键范围。

8kb数据页或索引页。

与一个区相邻的一组八个数据页或索引页。

包括所有数据和索引的整个表。

DB数据库。

4.锁定时间的长度。

持有锁的时间长度是在所请求的级别保护资源所需的时间长度。

用于保护读取操作的共享锁的保留时间取决于事务隔离级别。在默认事务隔离级别为READ COMMITTED的情况下，共享锁仅在读取页面期间受到控制。在扫描中，直到在扫描的下一页上获得锁，才会释放锁。如果指定HOLDLOCK提示或将事务隔离级别设置为REPEATABLE READ或SERIALIZABLE，则在事务结束之前不会释放锁。

根据为游标设置的并发选项，游标可以获得共享模式的滚动锁以保护fetch。当需要滚动锁定时，滚动锁定将不会被释放，直到下一次光标被提取或关闭(以先发生者为准)。但是，如果指定了HOLDLOCK，则直到事务结束时才会释放滚动锁。

在事务结束之前，用于保护更新的排他锁不会被释放。

如果一个连接试图获取一个锁，并且该锁与另一个连接控制的锁冲突，则试图获取该锁的连接将被阻塞，直到:

冲突锁被释放，连接获得请求的锁。

连接的超时间隔已经过期。默认情况下，没有超时间隔，但一些应用程序设置它来防止无限期等待。

5.SQL Server中锁的自定义

1处理死锁和设置死锁优先级

死锁(Deadlock)是多个用户申请不同的阻塞而导致的无休止的等待，因为申请者都拥有部分阻塞权，同时等待其他用户拥有部分阻塞权。

您可以使用SET DEADLOCK_PRIORITY来控制会话在发生死锁时的反应。如果两个进程都锁定了数据，而每个进程都不能释放自己的锁，直到其他进程释放自己的锁，也就是发生了死锁。

2处理超时并设置锁定超时持续时间。

@@LOCK_TIMEOUT返回当前会话的当前锁定超时设置，以毫秒为单位。

设置lock _ timeout设置，以允许应用程序设置语句等待阻塞资源的最长时间。当语句等待时间超过LOCK_TIMEOUT设置时，系统会自动取消被阻塞的语句，并向应用程序返回1222错误消息“已超过锁请求超时期限”。

例子

以下示例将锁定超时时间设置为1，800毫秒。

设置LOCK_TIMEOUT 1800

3)设置事务隔离级别。

4)对SELECT、INSERT、UPDATE和DELETE语句使用表级锁定提示。

5)配置索引的锁定粒度

可以使用sp_indexoption系统存储过程来设置索引的锁定粒度。

6.检查锁定信息。

1执行EXEC SP_LOCK以报告有关锁的信息。

2在查询分析器中按Ctrl+2查看锁信息。

七。使用注意事项

如何避免死锁

1使用事务时，尽量缩短事务的逻辑处理过程，尽早提交或回滚事务；

2将死锁超时参数设置在合理的范围内，如:3分钟到10分钟；如果超过时间，该操作将自动放弃，以避免进程暂停；

优化程序，检查并避免死锁；

4.在准确的版本之前，仔细测试所有的脚本和SP。

5所有sp都应具备错误处理功能(通过@error)

6通常，不要修改SQL SERVER事务的默认级别。不建议强行锁定。

如何解决问题如何锁定行表数据库
八个以上与锁相关的问题。

1如何锁定表格的一行？

将事务隔离级别设置为未提交读

SELECT * FROM table ROWLOCK，其中id = 1

2锁定数据库中的表。

SELECT * FROM table WITH (HOLDLOCK)

锁定语句:

赛贝斯:

更新集合col1=col1，其中1 = 0；

MSSQL:

Col1from table (tablocxx)其中1 = 0；

甲骨文:

以独占模式锁定表；

锁被锁定后，其他人无法操作，直到被锁定的用户解锁，用commit或rollback解锁。

几个例子帮助你加深印象。

设置表1(A、B、C)

公元前

a1 b1 c1

a2 b2 c2

a3 b3 c3

1)排他锁

创建两个新连接

在第一个连接中执行以下语句

开始事务

更新表1

设置A='aa '

其中B='b2 '

等待延迟' 00:00:30 '-等待30秒

提交交易

在第二个连接中执行以下语句

开始事务

从表1中选择*

其中B='b2 '

提交交易

如果同时执行上述两条语句，select查询必须等待更新完成，也就是说，它将等待30秒。

2)共享锁

在第一个连接中执行以下语句

开始事务

从表1中选择*保持锁定-保持锁定被手动锁定。

其中B='b2 '

等待延迟' 00:00:30 '-等待30秒

提交交易

在第二个连接中执行以下语句

开始事务

从表1中选择A、C

其中B='b2 '

更新表1

设置A='aa '

其中B='b2 '

提交交易

如果同时执行以上两条语句，则可以执行第二个连接中的select查询。

Update必须等第一个事务释放共享锁，变成独占锁后才能执行，也就是说要30秒才会变成
3)死锁。

增加表2(D，E)

有

d1 e1

d2 e2

在第一个连接中执行以下语句

开始事务

更新表1

设置A='aa '

其中B='b2 '

等待延迟' 00:00:30 '

更新表2

设置D='d5 '

其中E='e1 '

提交交易

在第二个连接中执行以下语句

开始事务

更新表2

设置D='d5 '

其中E='e1 '

等待延迟' 00:00:10 '

更新表1

设置A='aa '

其中B='b2 '

提交交易

同时，系统将检测到死锁并中止进程。

补充一点:

Sql Server2000支持的表级锁定提示

HOLDLOCK持有共享锁，直到整个事务完成，在不需要锁定对象时应立即释放，相当于可序列化事务隔离级别。

执行NOLOCK语句时，不发出共享锁，允许脏读，相当于READ UNCOMMITTED事务隔离级别。

PAGLOCK在使用一个表锁的情况下使用多个页锁。

READPAST让sql server跳过任何锁定的行并执行事务。它适用于读取未提交的事务隔离级别。只跳过RID锁，但不跳过页锁、区域锁和表锁。

ROWLOCK强制行锁。

Tabx强制执行一个排他的表级锁，防止任何其他事务在事务期间使用该表。

UPLOCK强制在不共享锁的情况下读取表时使用更新。

应用程序锁:

应用程序锁是由客户端代码生成的锁，而不是由sql server本身生成的锁。

处理应用程序锁的两个过程

Sp_getapplock锁定应用程序资源

Sp_releaseapplock解锁应用程序资源

注意:在数据库中锁定一个表

SELECT * FROM table WITH (HOLDLOCK)其他事务可以读取该表，但不能更新或删除它。

SELECT * FROM table WITH (TABLOCKX)其他事务不能读取、更新和删除表。