question

billgreen-4986 avatar image
0 Votes"
billgreen-4986 asked CarrinWu-MSFT commented

Read committed on ddl

Quick question? Table Hints , with(NoLock) and with (readuncommitted) are only able to bypass DML and Not DDL right?

Let’s say if I have something like

Begin Tran

Update dbo.Staff
Set contactID = 7
Where Name = Ivan

And I don’t rollback the transaction

Then someone else writes a query to access the records in the staff table.

Select *
From dbo.staff With (nolock)

They would be able to read the uncommitted data( which would be considered a dirty read) if the connection is set to read uncommitted.

On flip side though if I write a query that changes the structure of a table

Begin Tran

Alter Table dbo.staff Add column _G varchar(30)

And that query isn’t committed

And someone tries execute the following:


Select *
From dbo.staff With (nolock)

It won’t be able to bypass
due to it being DDL.

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @billgreen-4986, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
DanGuzman avatar image
1 Vote"
DanGuzman answered

Your understanding is correct. DDL like ALTER TABLE acquires a restrictive schema modification lock, which protects the meta-data associated with the object. All queries, including those with NOLOCK hints or SNAPSHOT isolation level, honor the lock.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @billgreen-4986,

Welcome to Microsoft Q&A!

Table Hints , with(NoLock) and with (readuncommitted) are only able to bypass DML and Not DDL right?

Yes, you are right. But the WITH (NOLOCK) and READUNCOMMITTED table hints can be only used with the SELECT statements, not all DML. If you try to use the WITH (NOLOCK) table hint in the DELETE statement, you will get an error, showing that it both the WITH (NOLOCK) and READUNCOMMITTED table hints are not allowed with the UPDATE, INSERT, DELETE or MERGE T-SQL statements. Please refer to this blog to get more details.


Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.