Several lock problems

Several lock problems
I: Yujohny)

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.

Ii. Strong
1. How to lock a row in a table

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.