Read the article to understand the working of BEGIN TRANSACTION and WITH (NOLOCK) in SQL Server. As part of our SQL Server Support, Bobcares provides answers to all of your questions.

BEGIN TRANSACTION and WITH (NOLOCK) in SQL Server

A new transaction in SQL Server starts with the BEGIN TRANSACTION command. However, in SQL Server, a “dirty read” is carried out using the WITH (NOLOCK) hint. It’s important to handle transactions correctly when using BEGIN TRANSACTION with NOLOCK. Changes should be rolled back (ROLLBACK) to remove them permanently, or committed (COMMIT) to make changes permanent. Transactions that are not saved or rolled back may cause locks to be kept longer than necessary, which may have an effect on concurrency and performance.

Let’s see how both of them work together:

1. With the BEGIN TRANSACTION statement, a transaction begins. The following SQL statements are handled as a single event from this point on. So, to influence the transaction’s result, we usually use BEGIN TRANSACTION along with COMMIT or ROLLBACK. All changes made during the transaction are stored in the database if we COMMIT it. We lose all modifications if we ROLLBACK.

2. A query that includes the WITH (NOLOCK) hint is allowed to read data from tables without having to wait for locks to be released. When running reports, for example, or in other situations when data consistency is not as important, this can enhance query performance. However, there are possible risks involved, such as reading inconsistent or unsaved data.

An Example

If other transactions are changing the data being read, then using WITH (NOLOCK) may result in incorrect data. As such, it should only be applied rarely and usually in situations when data consistency is not essential. Use different isolation levels or locking techniques to make sure we read and write data safely and reliably throughout the transactions if data consistency is crucial.

[Want to learn more? Click here to reach us.]

Conclusion

We can plan and simplify the SQL Server questions and transactions more effectively if we are aware of the effects of using BEGIN TRANSACTION with NOLOCK.