How to lock a row-table database and how to lock a row-table database

How to lock a row table database 1 how to lock a row of a table

SET transaction isolation level read uncommitted

Select * from Table rowlock where id = 1

2. Lock a table in the database

Select * from table with (holdlock)

Lock statement:
SYBASE:
Update table set col1 = col1 where 1 = 0;
MSSQL:
Select col1 from table (tablockx) where 1 = 0;
ORACLE:
Lock table in exclusive mode;
No one else can operate after the lock, until the locked user is unlocked and unlocked with commit or rollback

Several examples help you better understand
Set Table1 (A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

1) exclusive lock
Create two connections
Execute the following statement in the first connection:
Begin tran
Update Table1
Set a = 'A'
Where B = 'b2'
Waitfor delay '00: 00: 30' -- wait 30 seconds
Commit tran
Execute the following statement in the second connection
Begin tran
Select * From Table1
Where B = 'b2'
Commit tran

If the preceding two statements are executed at the same time, the SELECT query must wait 30 seconds until the update statement is executed.

2) shared lock
Execute the following statement in the first connection:
Begin tran
Select * From Table1 holdlock-holdlock artificial lock
Where B = 'b2'
Waitfor delay '00: 00: 30' -- wait 30 seconds
Commit tran

Execute the following statement in the second connection
Begin tran
Select a, c from Table1
Where B = 'b2'
Update Table1
Set a = 'A'
Where B = 'b2'
Commit tran

If the preceding two statements are executed simultaneously, the SELECT query in the second connection can be executed.
However, update can only be executed 30 seconds after the first transaction releases the shared lock and changes it to the exclusive lock.

3) deadlock
Add Table2 (D, E)
D e
D1 E1
D2 E2
Execute the following statement in the first connection:
Begin tran
Update Table1
Set a = 'A'
Where B = 'b2'
Waitfor delay '00: 00: 30'
Update Table2
Set d = 'd5'
Where E = 'e1'
Commit tran

Execute the following statement in the second connection
Begin tran
Update Table2
Set d = 'd5'
Where E = 'e1'
Waitfor delay '00: 00: 10'
Update Table1
Set a = 'A'
Where B = 'b2'
Commit tran

At the same time, the system detects a deadlock and terminates the process.

Add:
Table-level locking prompt supported by SQL Server

Holdlock holds the shared lock until the entire transaction is completed. It should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level.

The nolock statement does not issue a shared lock when it is executed. Dirty reads are allowed, which is equal to the read uncommitted transaction isolation level.

Paglock uses multiple page locks when a table lock is used

Readpast allows the SQL Server to skip any locked rows and execute transactions. This applies to the read uncommitted transaction isolation level, which only skips the RID lock and does not skip pages, regions, and table locks.

Rowlock force row lock

Tablockx forces the use of an exclusive table lock, which prevents any other transactions from using this table during the transaction.

Uplock forces the use of updates during table reading without sharing locks

Application lock:
The application lock is the lock generated by the client code, not the lock generated by the SQL server itself.

Two processes for processing application locks

Sp_getapplock: Lock application resources

Sp_releaseapplock unlock application resources

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

Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table.

Select * from table with (tablockx) other transactions cannot read, update, and delete tables.

1. How to lock a row in a table
/*
Test environment: Windows 2 k SERVER + MSSQL 2000
All functions have been tested and corresponding result sets are available. If you have any questions, follow the forum.
Copyright description: Some materials are from the Internet. If there is any improper situation, contact the moderator. The moderator will handle the issue immediately.
Function: SQL traverses the text file names in folders. Of course, you can complete the list of various files after modifying some code.
*/

Execute in connection

SET transaction isolation level Repeatable read

Begin tran

Select * From tablename with (rowlock) Where id = 3

Waitfor delay '00: 00: 05'

Commit tran

If you execute

Update tablename set colname = '10' where id = 3 -- Wait 5 seconds.

Update tablename set colname = '10' where ID <> 3 -- immediate execution

2. Lock a table in the database

Select * from table with (holdlock)

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

Select * from table with (holdlock)
Other transactions can read tables, but cannot update or Delete tables.

Select * from table with (tablockx)
Other transactions cannot read the table, update and delete 1. How to lock a row in a table

SET transaction isolation level read uncommitted

Select * from Table rowlock where id = 1

2. Lock a table in the database

Select * from table with (holdlock)

Lock statement:
SYBASE:
Update table set col1 = col1 where 1 = 0;
MSSQL:
Select col1 from table (tablockx) where 1 = 0;
ORACLE:
Lock table in exclusive mode;
No one else can operate after the lock, until the locked user is unlocked and unlocked with commit or rollback

Several examples help you better understand
Set Table1 (A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

1) exclusive lock
Create two connections
Execute the following statement in the first connection:
Begin tran
Update Table1
Set a = 'A'
Where B = 'b2'
Waitfor delay '00: 00: 30' -- wait 30 seconds
Commit tran
Execute the following statement in the second connection
Begin tran
Select * From Table1
Where B = 'b2'
Commit tran

If the preceding two statements are executed at the same time, the SELECT query must wait 30 seconds until the update statement is executed.

2) shared lock
Execute the following statement in the first connection:
Begin tran
Select * From Table1 holdlock-holdlock artificial lock
Where B = 'b2'
Waitfor delay '00: 00: 30' -- wait 30 seconds
Commit tran

Execute the following statement in the second connection
Begin tran
Select a, c from Table1
Where B = 'b2'
Update Table1
Set a = 'A'
Where B = 'b2'
Commit tran

If the preceding two statements are executed simultaneously, the SELECT query in the second connection can be executed.
However, update can only be executed 30 seconds after the first transaction releases the shared lock and changes it to the exclusive lock.

3) deadlock
Add Table2 (D, E)
D e
D1 E1
D2 E2
Execute the following statement in the first connection:
Begin tran
Update Table1
Set a = 'A'
Where B = 'b2'
Waitfor delay '00: 00: 30'
Update Table2
Set d = 'd5'
Where E = 'e1'
Commit tran

Execute the following statement in the second connection
Begin tran
Update Table2
Set d = 'd5'
Where E = 'e1'
Waitfor delay '00: 00: 10'
Update Table1
Set a = 'A'
Where B = 'b2'
Commit tran

At the same time, the system detects a deadlock and terminates the process.

Add:
Table-level locking prompt supported by SQL Server

Holdlock holds the shared lock until the entire transaction is completed. It should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level.

The nolock statement does not issue a shared lock when it is executed. Dirty reads are allowed, which is equal to the read uncommitted transaction isolation level.

Paglock uses multiple page locks when a table lock is used

Readpast allows the SQL Server to skip any locked rows and execute transactions. This applies to the read uncommitted transaction isolation level, which only skips the RID lock and does not skip pages, regions, and table locks.

Rowlock force row lock

Tablockx forces the use of an exclusive table lock, which prevents any other transactions from using this table during the transaction.

Uplock forces the use of updates during table reading without sharing locks

Application lock:
The application lock is the lock generated by the client code, not the lock generated by the SQL server itself.

Two processes for processing application locks

Sp_getapplock: Lock application resources

Sp_releaseapplock unlock application resources

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

Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table.

Select * from table with (tablockx) other transactions cannot read, update, and delete tables.

1. How to lock a row in a table
/*
Test environment: Windows 2 k SERVER + MSSQL 2000
All functions have been tested and corresponding result sets are available. If you have any questions, follow the forum.
Copyright description: Some materials are from the Internet. If there is any improper situation, contact the moderator. The moderator will handle the issue immediately.
Function: SQL traverses the text file names in folders. Of course, you can complete the list of various files after modifying some code.
*/

Execute in connection

SET transaction isolation level Repeatable read

Begin tran

Select * From tablename with (rowlock) Where id = 3

Waitfor delay '00: 00: 05'

Commit tran

If you execute

Update tablename set colname = '10' where id = 3 -- Wait 5 seconds.

Update tablename set colname = '10' where ID <> 3 -- immediate execution

2. Lock a table in the database

Select * from table with (holdlock)

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

Select * from table with (holdlock)
Other transactions can read tables, but cannot update or Delete tables.

Select * from table with (tablockx)
Other transactions cannot read, update, or delete tables.