SQL Server locks a table to limit that different things cannot operate on a table at the same time. The instance is simple. You can use select to lock the entire table, it is impossible for others to update or read Table records.
Select * From DBO. employee with (holdlock); With keyword to set the lock table method.
The title of the keyword in the with brackets is as follows:
Nolock (no lock)
When this option is selected, SQL server does not apply any lock when reading or modifying data. In this case, the user may read the data in the uncommitted transaction or roll back, that is, the so-called "dirty data ".
Holdlock)
When this option is selected, SQL Server will keep the shared lock until the end of the entire transaction, instead of releasing it on the way.
Updlock)
When this option is selected, SQL server uses the modification lock to replace the shared lock when reading data, and keeps the lock until the entire transaction or command ends. This option ensures that multiple processes can read data at the same time, but only the process can modify data.
Tablock)
When this option is selected, SQL Server sets a shared lock on the entire table until the command ends. This option ensures that other processes can only read but cannot modify data.
Paglock)
This option is the default option. When selected, SQL server uses the share page lock.
Tablockx (exclusive table lock)
When this option is selected, SQL Server will lock the entire table until the command or transaction ends. This prevents other processes from reading or modifying table data.
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.
For tabllockx, even reading is blocked before the transaction is committed and can be read only after another transaction is committed. This ensures data consistency.
Valuable references
SQL Server locking)
SQL Server ------ lock
Introduction to locking in SQL Server