Shulou(Shulou.com)06/01 Report--

Official document https://docs.microsoft.com/zh-cn/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

TABLOCKX

SELECT * FROM table WITH (TABLOCKX)

During the query process, other sessions cannot query and update this table until the query process is over

XLOCK

SELECT * FROM table WITH (XLOCK)

During the query process, other sessions cannot query and update this table until the query process is over

UPDLOCK

SELECT * FROM table WITH (UPDLOCK)

During the query process, other sessions can query, but cannot update this table until the query process is over

TABLOCK

SELECT * FROM table WITH (TABLOCK)

During the query process, other sessions can query, but cannot update this table until the query process is over

HOLDLOCK

SELECT * FROM table WITH (HOLDLOCK)

During the query process, other sessions can query, but cannot update this table until the query process is over

NOLOCK

SELECT * FROM table WITH (NOLOCK)

During the query process, other sessions can query and update this table

The difference between NOLOCK, (NOLOCK) and WITH (NOLOCK):

SELECT * FROM TEST1 NOLOCK

SELECT * FROM TEST1 (NOLOCK)

SELECT * FROM TEST1 WITH (NOLOCK)

1: NOLOCK is really just an alias for a table and has no real effect.

2: (NOLOCK) is functionally the same as WITH (NOLOCK). (NOLOCK) is an alias for WITH (NOLOCK).

The data for TEST1 are as follows

SELECT * FROM TEST1

Hid hid2

1 10001

2 10002

Session 1

BEGIN TRAN

UPDATE TEST1 SET hid=9 WHERE hid= 1

Session 2 is blocked.

SELECT * FROM TEST1

Session 3 is blocked.

SELECT * FROM TEST1 NOLOCK

Session 4 is normal, but the data queried is dirty, and hid=9 is data that is neither committed nor rolled back.

SELECT * FROM TEST1 with (nolock)

Hid hid2

9 10001

2 10002

SELECT * FROM TEST1 (nolock)

Hid hid2

9 10001

2 10002

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.