Sqlserver lock table, unlock, view sale table

Source: Internet
Author: User
In sqlserver, how can I lock tables, unlock tables, and view sale tables? I will introduce three different instances to my friends for details.

In sqlserver, how can I lock tables, unlock tables, and view sale tables? I will introduce three different instances to my friends for details.

In sqlserver, how can I lock tables, unlock tables, and view sale tables? I will introduce three different instances to my friends for details.

Lock a table in the database

The Code is as follows:

SELECT * FROM table WITH (HOLDLOCK)

Note: What is the difference between locking a database table?

The Code is as follows:

SELECT * FROM table WITH (HOLDLOCK)

Other transactions can read tables, but cannot update or Delete tables.

The Code is as follows:

SELECT * FROM table WITH (TABLOCKX)

Other transactions cannot read, update, or delete tables.

Description of the "Lock options" function in the SELECT statement

SQL Server provides a powerful and complete locking mechanism to help achieve database system concurrency and high performance. You can use the default settings of SQL Server or the "Lock option" in the select statement to achieve the expected results. This article introduces the "Lock options" in the SELECT statement and related functions.


View the locked table:

The Code is as follows:

Select request_session_id spid, OBJECT_NAME (resource_associated_entity_id) tableName
From sys. dm_tran_locks where resource_type = 'object'

Spid lock table process
TableName: name of the table locked


Unlock:

Create a temporary Table

The Code is as follows:
Create table # HarveyLock
(
Spid int,
Dbid int,
Objid int,
Indid int,
Type varchar (100 ),
Resource varchar (1, 100 ),
Mode varchar (100 ),
Status varchar (100)
)


Store the Lock information to the Table.

Insert into # HarveyLock EXEC SP_LOCK

3. The LOCK obtained by the condition query in the Table
SELECT * FROM # HarveyLock

4. KILL LOCK
KILL @ SPID

Example

The Code is as follows:

Declare @ spid int
Set @ spid = 57 -- lock table process
Declare @ SQL varchar (1000)
Set @ SQL = 'Kill '+ cast (@ spid as varchar)
Exec (@ SQL)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.