Sqlserver locks a row of data, prohibit reading, modifying, and deleting, and a row

Sqlserver locks a row of data, prohibit reading, modifying, and deleting, and a row

In SQL transactions, you sometimes need to lock a row and release the row after the transaction is completed.

Use with (ROWLOCK, UpdLock) to lock the current row. Prohibit reading, modifying, and deleting

Transaction

Alter procedure [dbo]. [asuo]

AS
BEGIN


Begin tran
Select top 1 * FROM dbo. JYQ_HLT_Order with (ROWLOCK, UpdLock) WHERE orderID = 321878.
UPDATE dbo. JYQ_HLT_Order SET orderBillingstatus = 13 WHERE orderID = 321878.
Select top 1 * FROM dbo. JYQ_HLT_Order WHERE orderID = 321878.
Waitfor delay '00: 00: 10'
COMMIT TRAN
END

Transaction B

Alter procedure [dbo]. [bsuo]

AS
BEGIN
Begin tran
UPDATE dbo. JYQ_HLT_Order SET orderBillingstatus = 14 WHERE orderID = 321878.
Select top 1 * FROM dbo. JYQ_HLT_Order WHERE orderID = 321878.
COMMIT TRAN
END

Transaction B is executed only after transaction a COMMIT TRAN.

With (ROWLOCK) any function

With (ROWLOCK, UpdLock) must be used in combination to take effect