SQL查询：了解数据库锁隔离级别 (sql 查询 数据库锁隔离级别)

在数据库中，锁是非常重要的机制。锁的实现可以提供并发访问时的数据完整性和一致性，确保多线程或多进程同时操作数据库时，数据的正确性不会受到影响。而锁隔离级别则是指数据库为了满足并发控制要求，而规定的不同事务之间的隔离等级。

数据库的事务并发控制是通过锁的机制实现的，可分为悲观锁和乐观锁。在悲观锁机制下，数据的操作需要先获得相应的锁，只要有一个事务持有锁，其他的事务就必须等待，这个等待过程就是阻塞过程，所以悲观锁机制下，多个事务同时并发执行时，性能会受到很大的影响。而在乐观锁机制下，首先不会有阻塞过程，而是先对数据进行操作，当需要提交时，才进行数据的校验，如果数据校验通过，则事务提交成功，否则进行回滚。

不同的数据库的锁隔离级别定义略有区别，但大体可以分为以下四种（从低到高）：

1. 读未提交（Read Uncommitted）：这是更低的隔离级别，该级别下，一个事务可以读取另一个事务未提交的数据，可能导致脏读、不可重复读和幻读的问题。

2. 读已提交（Read Committed）：在该级别下，一个事务只能读取已经提交的数据，避免了脏读的问题，但同时可能导致不可重复读和幻读的问题，比如在一个事务更新时，另一个事务读取该数据时，可能还未提交事务的更新，导致读出的结果与该事务提交后读取的结果不一致。

3. 可重复读（Repeatable Read）：该隔离级别下，一个事务可以多次读取相同的数据，保证了同一事务中，数据的可重复读取性，但同时可能会导致幻读的问题。幻读是指某一个事务（通常是insert）在读到一组数据（比如某个范围内的所有记录）后，在进行修改时，另一个事务（通常也是insert）在之前没有出现的数据同时更新，在之一个事务再次查看数据时，发现了之前没有的数据。

4. 串行化（Serializable )：该级别下，所有的事务只能串行执行，因此可以避免脏读、不可重复读和幻读的问题。但同时也是效率更低的。

在实际使用中，我们应该根据具体场景来选择合适的锁隔离级别。如果强一致性要求不是特别高，可以选择Read Committed隔离级别，如果要求强一致性，则可以选择Repeatable Read隔离级别；如果对性能要求非常高，Query-by-consistency (QBC) 也许更适合你。

数据库锁隔离级别的选择需要根据应用场景、性能、数据一致性等方面来综合考虑。正确的选择可以提高系统的并发性、可用性和性能，而错误的选择则可能导致各种问题和瓶颈。

相关问题拓展阅读：

• 如何解除sql server数据库数据被锁定
• 如何处理SQL Server死锁问题

如何解除sql server数据库数据被锁定

(1)

HOLDLOCK:

在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

(2)

NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

(3)

PAGLOCK：指定添加页锁（否则通常可能添加表锁）。

(4)

READCOMMITTED用与运行在提交读隔离亩改级别的事务相同的锁语义执行扫描。默认情况下，SQL

Server

2023

在此隔离级别上操作。

(5)

READPAST:

跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，

READPAST仅仅应用于READ

COMMITTED隔离性级别下事务操作中的SELECT语句操作。

(6)

READUNCOMMITTED：等同于NOLOCK。

(7)

REPEATABLEREAD：设置事务为可重复读隔离性级别。

(8)

ROWLOCK：使用行级余耐没锁，而不使用粒度更粗的页级锁和表级锁。

(9)

SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于

HOLDLOCK。

(10)

TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL

Server在该语句执行完后释放这个锁，而如果同时指定了…(1)

HOLDLOCK:

在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

(2)

NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

(3)

PAGLOCK：指定添加页锁（否则通常可能添加表锁）。

(4)

READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL

Server

2023

在此隔离级别上操作。

(5)

READPAST:

跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，

READPAST仅仅应用于READ

COMMITTED隔离性级别下事务操作中的SELECT语句操作。

(6)

READUNCOMMITTED：等同于NOLOCK。

(7)

REPEATABLEREAD：设置事务为可重复读隔离性级别。

(8)

ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。

(9)

SERIALIZABLE：用与运行在可串行读隔竖纳离级别的事务相同的锁语义执行扫描。等同于

HOLDLOCK。

(10)

TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL

Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。

(11)

TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。

(12)

UPDLOCK

：指定在

读表中数据时设置更新

锁（update

lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。

如何处理SQL Server死锁问题

1)

预防死锁。

这是一种较简单和直观的事先预防的方法。方法是通过设置某些限制条件，去破坏产生死锁的四个必要条件中的一个或者几个，来预防发生死锁。预防死锁是一种较易实现的方法，已被广泛使用。但是由于所施加的限制条件往往太严格，可能会导致系统资源利用率和系统吞吐量降低。

2)

避免死锁。

该方法同样是属于事先预防的策略，但它并不须事先采取各种限制措施去破坏产生死锁的的四个必要条件，而是在资源的动态分配过程中，用某种方法去隐源防止系统进入不安全状态，从而避免发生死锁。

3)检测死锁。

这种方法并不须事先采取任何限制性措施，也不必检查系统是否已经进入不安全区，此方法允灶春态许系统在运行过程中发生死锁。但可通过系统所设置的检测机构，及时地检测出死锁的发生，并精确地确定与死锁有关的进程和资源，然后采取适当措施，从系统中将已发生的死锁清除掉。

4)解除死锁。

这是与检测死锁相配套的一种措施。当检测到系统中已发生死锁时，须将进程从死锁状态中解脱出来。常用的实施方法是撤销或挂起一些进程，以便回收一些资源，再将这些资源分配给已处于阻塞状态的进程，使之转为就绪状态，以继续运行。死锁的检测和解除措施，有可能使系统获得较好的资源利用率和吞吐量，但在实现上难度也更大。

由上面4中处理死锁的办法看，其中检测死锁和解除死锁是Lock

Monitor的事，作为DBA或数据库开发人员，处理死锁要放在预防和避免死锁上。

预防死锁

预防死锁就是破坏四个必要条件中的某一个和几个，使其不能形成死锁。有如下几种办法

1)破坏互斥条件

破坏互斥条件有比较严格的限制,在SQL

Server中，如果业务逻辑上允许脏读，则可以通过将隔离等级改为未提交读或使用索引提示。这样使得读取不用加S锁，从而避免了和其它查询所加的与S锁不兼容的锁互斥，进而减少了死锁出现的概率。

2)破坏请求和等待条件

这点由于事务存在原子性，是不可破坏的，因为解森衡决办法是尽量的减少事务的长度，事务内执行的越快越好。这也可以减少死锁出现的概率。

3)破坏不剥夺条件

由于事务的原子性和一致性，不剥夺条件同样不可破坏。但我们可以通过增加资源和减少资源占用两个角度来考虑。

增加资源：比如说通过建立非聚集索引，使得有了额外的资源，查询很多时候就不再索要锁基本表，转而锁非聚集索引，如果索引能够“覆盖(Cover)”查询，那更好不过。因此索引Include列不仅仅减少书签查找来提高性能，还能减少死锁。增加资源还可以通过SQL

Server

2023之后的行版本控制进行，但这种方式并不推荐，在此不再详细讨论。

减少资源占用:比如说查询时，能用select

col1,col2这种方式，就不要用select

*

.这有可能带来不必要的书签查找

死锁，简而言之，两个或者多个trans，同时请求对方正在请求的某个对象，导致双野物方互相等待。简单的例子如下：

trans trans2

—–

1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction

2.update table A2.update table B

3.update table B3.update table A

4.IDBConnection.Commit 4.IDBConnection.Commit

那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。

好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。

— Batch #1

CREATE DATABASE deadlocktest

GO

USE deadlocktest

SET NOCOUNT ON

DBCC TRACEON (1222, -1)

— 在SQL2023中，增加了一个新的dbcc参数，就是1222，原来在2023下，我们知道，可以执行dbcc

–traceon(1204,3605,-1)看到所有的死锁信息。SqlServer 2023中，颂判液对于1204进行了增强，这就是1222。

GO

IF OBJECT_ID (‘t1’) IS NOT NULL DROP TABLE t1

IF OBJECT_ID (‘p1’) IS NOT NULL DROP PROC p1

IF OBJECT_ID (‘p2’) IS NOT NULL DROP PROC p2

GO

CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))

GO

DECLARE @x int

SET @x = 1

WHILE (@x = AND . = AND … +(1)) ORDERED FORWARD)

哦，对于clustered index的需求没有了，因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。

实际上，在sqlserver 2023中，如果用profiler来抓eventid：1222，那么会出现一个死锁的图，很直观的说。

下面的方法，有助于将死锁减至最少（详细情况，请看SQLServer联机帮助，搜索：将死锁减至最少即可。

按同一顺序访问对象。

避免事务中的用户交互。

保持事务简短并处于一个批处理中。

使用较低的隔离级别。

使用基于行版本控制的隔离级别。

将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。

使用快照隔离。

sql 查询 数据库锁隔离级别的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于sql 查询 数据库锁隔离级别,SQL查询：了解数据库锁隔离级别,如何解除sql server数据库数据被锁定,如何处理SQL Server死锁问题的信息别忘了在本站进行查找喔。

香港服务器首选树叶云，2H2G首月10元开通。
树叶云（www.IDC.Net）提供简单好用，价格厚道的香港/美国云服务器和独立服务器。IDC+ISP+ICP资质。ARIN和APNIC会员。成熟技术团队15年行业经验。