Locks used in SQL Server Query statements

Some time ago, ** the DBA of the company came to our training. I talked about a lot of MYSQL optimizations. QA Stage 1: the programmer asked: "Can the with nolock in SQL statements read other locked data except for non-locked tables"

The lecturer muttered for a long time and did not explain clearly. It may be that MYSQL does not have this mechanism.) Another programmer of the company gave a very concise and clear answer.

With nolock will not be affected by other existing locks, but will not apply any locks to the table itself. The locked row data will also be read.

I personally think this sentence is clear and clear.

But the strange thing is that programmers often use this statement and don't even give it a try.

Here, we will summarize the with lock levels in other SQLSERVER.

• With nolock: No lock
• With holdlock: Hold a lock
• With updlock: An Update lock is attached.
• With xlock: mounting an exclusive lock

It should be noted that with nolock cannot be used for the update statement, such as delete insert. To put it simply, with nolock can only be used for select

For example, update dbo. test with (NOLOCK) set username = 'wokofo' -- This statement is incorrect.

Bounce: The target table of the INSERT, UPDATE, DELETE, or MERGE statement cannot use the NOLOCK or READUNCOMMITTED lock prompt.

Actual use:

 
 

  
  
select top 10 * from dbo.test with(NOLOCK) 
  
  
select top 10 * from dbo.test with(HOLDLOCK) 
  
  
select top 10 * from dbo.test with(XLOCK) 
  
  
select top 10 * from dbo.test with(UPDLOCK) 
  
  
  
  
  
  
  
  
update dbo.test with(HOLDLOCK) set username='wokofo' 
  
  
update dbo.test with(XLOCK) set username='wokofo' 
  
  
update dbo.test with(UPDLOCK) set username='wokofo'