No lock hints to avoid locking during reading in Microsoft SQL Server
We can avoid locking with help of HINT with (NOLOCK) if we are using it for reporting purposes which will allow dirty read of data that data may be rollbacked during transactions or never exist in the database.
Example of default behavior:
Suppose one transaction is updating a table in 1st SESSION and 2nd SESSION is trying to read the same table. Then SELECT statement has to wait for the table until its transaction is committed or rollbacked.
SESSION 1: BEGIN TRAN UPDATE [Person].[Address] SET City = 'DELHI' WHERE AddressID = 70 SESSION 2: (keep running and waiting) select * from [Person].[Address]
Check session 2 is waiting with help of SP_WHO2 or follow query:
Check waiting of transaction for session 2: SELECT r.session_id,st.TEXT AS batch_text,r.start_time,r.status, r.total_elapsed_time FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE DB_NAME(r.database_id) = 'AdventureWorks2022' ORDER BY cpu_time DESC;
Session 2: Select statement is waiting for Session 1 to complete its transaction, We can avoid this wait with help of NOLOCK hint
SESSION 3: Will run the same statement during transactions is running in SESSION 1.
SESSION 3:(Query return result immediate without wait for transaction to complete) select * from [Person].[Address] with (NOLOCK)
Session 3 Output is not waiting for returns the result for transactions to complete.
Note: Nolock help in reporting for application, if we allow some time dirty reads.