Mysql Create View With Set Transaction Isolation Level Read Uncommitted ;

SQL Server table hints are a special blazon of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is achieved past enforcing a specific locking method, a specific index or query processing functioning, such index seek or tabular array browse, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can exist added to the FROM clause of the T-SQL query, affecting the tabular array or the view that is referenced in the FROM clause only.

One of the more than heavily used table hints in the SELECT T-SQL statements is the WITH (NOLOCK) hint. The default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed. The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, past allowing the user to retrieve the data without beingness affected past the locks, on the requested information, due to another procedure that is changing it. In this way, the query volition swallow less retentivity in property locks confronting that data. In addition to that, no deadlock will occur against the queries, that are requesting the same data from that table, assuasive a higher level of concurrency due to a lower footprint. In other words, the WITH (NOLOCK) table hint retrieves the rows without waiting for the other queries, that are reading or modifying the aforementioned data, to stop its processing. This is similar to the READ UNCOMMITTED transaction isolation level, that allows the query to see the data changes before committing the transaction that is changing it. The transaction isolation level can be ready globally at the connectedness level using the SET TRANSACTION ISOLATION LEVEL T-SQL control, as will see later on in this article.

Although the NOLOCK table hint, like to all other tabular array hints, can exist used without using the WITH keyword, Microsoft announced that omitting the WITH keyword is a deprecated characteristic and volition be removed from time to come Microsoft SQL Server versions. With that said, it is better to include the WITH keyword when specifying the table hints. I benefit of using the WITH keyword is that you tin can specify multiple table hints using the WITH keyword against the same tabular array.

In general, using explicit table hints frequently is considered as a bad practice that you should generally avert. For the NOLOCK table hint specifically, reading uncommitted information that could exist rolled dorsum after you have read it can lead to a Dirty read, which can occur when reading the data that is being modified or deleted during the uncommitted information read, then that the data you read could be unlike, or never even accept existed.

The WITH (NOLOCK) tabular array hint also leads to Nonrepeatable reads; this read occurs when it is required to read the same data multiple times and the information changes during these readings. In this case, y'all will read multiple versions of the aforementioned row.

Phantom reads can be also a result of using the WITH(NOLOCK) table hint, in which you will get more records when the transaction that is inserting new records is rolled back, or fewer records when the transaction that is deleting existing data is rolled back. Another problem that may occur when other transactions motion information you lot have non read yet to a location that you lot take already scanned, or take added new pages to the location that y'all already scanned. In this case, you will miss these records and will not see information technology in the returned result. If another transaction moves the data that you have already scanned to a new location that you lot accept non read yet, you will read the data twice. Also, as the requested data could be moved or deleted during your reading process, the below error could be faced:

Msg 601, Level 12, State 1
Could not continue browse with NOLOCK due to data movement.

The WITH (NOLOCK) table hint is a good idea when the system uses explicit transactions heavily, which blocks the information reading very frequently. The WITH (NOLOCK) tabular array hint is used when working with systems that have out of sync information, such as the reporting systems.

To sympathize the usage of the WITH (NOLOCK) table hint practically, permit us create a new tabular array using the CREATE Table T-SQL argument beneath:

Utilize SQLShackDemo GO CREATE Tabular array LockTestDemo ( ID INT IDENTITY ( 1 , 1 ) Chief Central , EmpName NVARCHAR ( 50 ) , EmpAddress NVARCHAR ( 4000 ) , PhoneNumber VARCHAR ( 50 ) )

Afterward creating the table, we will fill it with 100K rows for testing purposes, using ApexSQL Generate, SQL test data generator, equally shown in the snapshot below:

One time the table is ready, we will simulate a blocking scenario, in which an update transaction volition be executed within a transaction that will begin and not committed or rolled back. The beneath Brainstorm TRAN T-SQL statement will start the transaction that will run the following UPDATE statement on the LockTestDemo table under SQL session number 53, without closing the transaction past committing or rolling it dorsum:

BEGIN TRAN UPDATE LockTestDemo SET EmpAddress = 'AMM' WHERE ID < 100

With the table's data locked by the transaction, nosotros will run another SELECT statement, under SQL session number 54, that retrieves data from the LockTestDemo table, using the SELECT statement beneath:

SELECT * FROM LockTestDemo

You volition see that the previous SELECT statement volition take a long time without retrieving whatsoever records. Checking what is blocking that SELECT query using sp_who2 command with the session number for both the SELECT and the UPDATE statements:

The result will show you that, the previously opened transaction is not performing whatever action, as the UPDATE statement executed successfully. Merely due to the fact that the transaction is not committed or rolled back even so, it still blocking other queries that are trying to get data from that tabular array. And the SELECT statement that is running under session 54 is blocked past that transaction that is running under session 53, equally shown in the result below:

The previous SELECT statement volition keep waiting for the transaction to be killed, committed or rolled back in order to get the requested rows from that table. Yous tin can terminate the transaction that is running under session 53 from blocking other queries by killing that session using the Kill control beneath:

Or simply committing or rolling back that transaction, by running the COMMIT or ROLLBACK command under the same session of the transaction, if applicable, equally shown below:

Once the locking is released, y'all will come across that the requested rows will be retrieved from the SELECT argument direct every bit shown in the results below:

The previous solution is non always preferable or applicable, for case, when the transaction that is blocking our queries is critical and not piece of cake to be killed or rolled dorsum, or when y'all don't have control over other'southward transactions within the database. In this case, the WITH (NOLOCK) table hint is useful here, if you can tolerate the risk of dirty reads or data inconsistency. As mentioned previously, the WITH (NOLOCK) table hint allows you to read the information that has been changed, but non committed to the database still. If you run the aforementioned SELECT statement without killing, committing or rolling back the UPDATE transaction, merely this fourth dimension adding the WITH (NOLOCK) table hint to the table name in the SELECT statement every bit shown below:

SELECT * FROM LockTestDemo WITH ( NOLOCK )

And then checking the SELECT statement status using the sp_who2 command. You lot volition come across that the query is running without waiting for the UPDATE transaction to be completed successfully and release the locking on the table, as shown in the snapshot below:

The WITH (NOLOCK) table hint works the same as the READUNCOMMITTED tabular array hint, allowing u.s. to retrieve the data that is changed but not committed still. The same SELECT statement can exist modified to utilise the READUNCOMMITTED tabular array hint equally shown below:

SELECT * FROM LockTestDemo WITH ( READUNCOMMITTED )

Retrieving the requested data straight, without waiting for the UPDATE statement to release the lock it performed on the tabular array, returning the same event as shown in the result set below:

Take into consideration that, the WITH (NOLOCK) and READUNCOMMITTED table hints tin be only used with the SELECT statements. If you effort to use the WITH (NOLOCK) table hint in the DELETE statement, you volition go an error, showing that it both the WITH (NOLOCK) and READUNCOMMITTED tabular array hints are not allowed with the UPDATE, INSERT, DELETE or MERGE T-SQL statements, as shown below:

Rather than allowing a dirty read at the query level using the WITH (NOLOCK) and READUNCOMMITTED table hints, you lot can change the transaction isolation level at the connection level to be READ UNCOMMITTED using the SET TRANSACTION ISOLATION LEVEL T-SQL statement below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SELECT * FROM LockTestDemo

This query will as well retrieve the same data straight, without using whatever table hint and without waiting for the UPDATE statement to release the lock information technology performed on the table, equally shown in the outcome ready below:

From the previous results, yous may think that this is the perfect solution for such scenarios, where you will get the requested data faster, without waiting for other operations to be committed, taking the run a risk of having non accurate data. But will the SELECT query that is using the WITH (NOLOCK) table hint negatively affects other processes on the SQL Server? To get the respond, let us commencement check what type of locks the WITH (NOLOCK) table hint will be granted during its execution. This can be achieved by simply running the sp_lock command with the session number of the running query, while the query is running, every bit shown below:

You lot will see from the outcome that the query that is using the WITH (NOLOCK) table hint volition be granted Due south and Sch-Southward locking types, as shown in the result below:

From the previous result, you will see that the WITH (NOLOCK) table hint volition be granted shared access (S) lock at the database level. The shared access (S) lock is used for reading performance, assuasive concurrent transactions to read information under pessimistic concurrency control, preventing other transactions from modifying the locked resource while shared (S) locks be on that resource, until that locking is released as soon equally the read operation completes.

The second kind of locking that is granted to the query using the WITH (NOLOCK) table hint is the schema stability (Sch-Southward) lock. This lock will not forestall whatever other transaction from accessing the resources except for the concurrent DDL operations, and concurrent DML operations that acquire schema modification (Sch-Grand) locks on the aforementioned table, that will be blocked while the query is executing. This really makes sense, as yous do non demand to offset reading data from the table then another transaction changes the structure of that table during your information retrieval process. SQL Server Database Engine uses the schema modification (Sch-M) locks while processing the data definition linguistic communication (DDL) commands, such as adding a new column, dropping an existing column, dropping or rebuilding indexes, to prevent concurrent access to the table, until the lock is released.

My NOLOCK query is blocking!

This means that the NOLOCK naming is non always 100% accurate. Use of the WITH (NOLOCK) table hint, that holds schema stability (Sch_S) lock, can block other queries that attempt to acquire a schema modification (Sch-M) lock on that table. It is a critical issue that yous should accept into consideration if there are lots of users executing their SELECT queries using the WITH (NOLOCK) table hint, preventing you from making any changes to the table schema or maintenances on the table indexes, being blocked by the schema stability (Sch_S) lock.

Presume that nosotros need to run the below SELECT statement, that is using the WITH (NOLOCK) table hint, under session number 53:

SELECT * FROM LockTestDemo WITH ( NOLOCK ) WHERE EmpAddress Like '%Da%' and EmpName like '%And%'

At the same fourth dimension, nosotros will run the below query, that is dropping an index on the same table and create it again, under session number 58:

USE [ SQLShackDemo ] Get DROP INDEX [ IX_LockTestDemo_EmpName ] ON [ dbo ] . [ LockTestDemo ] Go CREATE NONCLUSTERED Alphabetize [ IX_LockTestDemo_EmpName ] ON [ dbo ] . [ LockTestDemo ] ( [ EmpName ] ASC ) GO

Then checking the status of both queries using the sp_who2 command, y'all will run into from the result that, the SELECT statement that is using the WITH (NOLOCK) tabular array hint and running session number 53, is locking the DROP/CREATE Index process running under session number 58, as shown clearly beneath:

If we check the locks that are performed by each query, using the sys.dm_tran_locks organization object as in the query below:

SELECT * FROM sys . dm_tran_locks WHERE resource_type = 'OBJECT'

You lot will see that, the DROP/CREATE INDEX process running under session number 58 is waiting to acquire schema modification (Sch-M) lock type. This occurs due to the fact that, the schema modification (Sch-M) lock cannot be acquired while the schema stability (Sch_S) lock that is already granted to the SELECT statement running nether session number 53, already exists equally shown in the snapshot beneath:

My NOLOCK query is blocked!

Conversely, since the WITH (NOLOCK) tabular array hint acquires schema stability (Sch-South) lock blazon, the SELECT statement that is using the WITH (NOLOCK) table hint will be blocked if a schema modification is performed on that table. Presume that we run the below Modify TABLE T-SQL statement to change the size of the EmpAddress cavalcade on the LockTestDemo table, under session number 53:

Change Tabular array LockTestDemo Alter COLUMN [ EmpAddress ] VARCHAR ( 5000 )

At the same time, the below SELECT statement that is using the WITH (NOLOCK) table hint will be running under session number 54:

SELECT * FROM LockTestDemo WITH ( NOLOCK )

Checking the status of both queries using the sp_who2 commands below:

You will see that the SELECT statement running under session 54 is blocked by the ALTER Table statement running under session 54, equally shown below:

Then checking the locks that are performed past each query, using the sys.dm_tran_locks system object every bit in the query below:

SELECT * FROM sys . dm_tran_locks WHERE resource_type = 'OBJECT' AND request_session_id in ( 53 , 54 )

It will be clear from the returned result that, the SELECT statement that is using the WITH (NOLOCK) tabular array hint and running nether session number 54, volition be waiting to larn schema stability (Sch_S) lock, due to the fact that the schema stability (Sch-South) lock cannot be acquired while the schema modification (Sch_M) lock, that is already granted to the Change statement running under session number 53, already exists as shown in the snapshot below:

You can imagine the situation when you are scheduling huge number of reports at night, that are using the WITH (NOLOCK) table hint just to exist safe. At the aforementioned time, there are maintenance jobs that are also scheduled to rebuild heavily fragmented indexes on the same table!

In that location are number of best practices and suggestions that you lot tin can follow, in order to avoid the problems that you may face when using WITH (NOLOCK) tabular array hint. Such suggestions include:

• Include but the columns that are actually required in your SELECT query
• Make sure that your transaction is brusque, past separating unlike operations from each other. For example, practise not include a huge SELECT statement between two UPDATE operations
• Try to find an alternative to the cursors
• Have care to use and benefit from the newly divers WAIT_AT_LOW_PRIORITY option to practice an online rebuild for the indexes
• Study reporting vs maintenances schedules well
• Take care to apply and benefit from the unlike SQL Server loftier availability solutions for reporting purposes, such every bit:
  • Configure the Always On Availability Groups secondary replicas to be readable and use it for reporting
  • Create database snapshots when using the SQL Server Database Mirroring and use it for reporting
  • Use the SQL Server Replication subscriber database for reporting
  • Employ the secondary database of the SQL Server Log Shipping for reporting

• Author
• Recent Posts

Ahmad Yaseen is a Microsoft Large Data engineer with deep knowledge and feel in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Information Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Acquaintance and Microsoft Certified Trainer.

Too, he is contributing with his SQL tips in many blogs.

View all posts past Ahmad Yaseen

stokesbadmight.blogspot.com

Source: https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/

Share this post