Home Database Mysql Tutorial SqlServer表死锁的解决方法分享

SqlServer表死锁的解决方法分享

Jun 07, 2016 pm 06:02 PM
sqlserver

前些天写一个存储过程,存储过程中使用了事务,后来我把一些代码注释掉来进行调试找错,突然发现一张表被锁住了,原来是创建事务的代码忘记注释掉。本文表锁住了的解决方法。

其实不光是上面描述的情况会锁住表,还有很多种场景会使表放生死锁,解锁其实很简单,下面用一个示例来讲解:

1 首先创建一个测试用的表:
代码如下:
CREATE TABLE Test
(
TID INT IDENTITY(1,1)
)

2 执行下面的SQL语句将此表锁住:
代码如下:
SELECT * FROM Test WITH (TABLOCKX)

3 通过下面的语句可以查看当前库中有哪些表是发生死锁的:
代码如下:
SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT '

4 上面语句执行结果如下:


spid :被锁进程ID。
tableName:发生死锁的表名。

5 只需要使用kill关键字来杀掉被锁的进程ID就可以对表进行解锁:

KILL 52
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to solve the problem that the object named already exists in the sqlserver database How to solve the problem that the object named already exists in the sqlserver database Apr 05, 2024 pm 09:42 PM

How to solve the problem that the object named already exists in the sqlserver database

How to import mdf file into sqlserver How to import mdf file into sqlserver Apr 08, 2024 am 11:41 AM

How to import mdf file into sqlserver

What to do if the sqlserver service cannot be started What to do if the sqlserver service cannot be started Apr 05, 2024 pm 10:00 PM

What to do if the sqlserver service cannot be started

Where is the sqlserver database? Where is the sqlserver database? Apr 05, 2024 pm 08:21 PM

Where is the sqlserver database?

How to recover accidentally deleted database in sqlserver How to recover accidentally deleted database in sqlserver Apr 05, 2024 pm 10:39 PM

How to recover accidentally deleted database in sqlserver

How to check sqlserver port number How to check sqlserver port number Apr 05, 2024 pm 09:57 PM

How to check sqlserver port number

How to solve Java connection SqlServer error How to solve Java connection SqlServer error May 01, 2023 am 09:22 AM

How to solve Java connection SqlServer error

How to delete sqlserver if the installation fails? How to delete sqlserver if the installation fails? Apr 05, 2024 pm 11:27 PM

How to delete sqlserver if the installation fails?

See all articles