READ COMMITTED: One step up (and the default for SQL Server). A query in the current transaction can’t read data modified by someone else that hasn’t yet committed. No dirty reads. BUT….data could be changed by others between statements in the current transaction, so the data may not look the same twice. READ COMMITTED uses shared locks to prevent dirty reads, but that’s about all you get. You still get non-repeatable reads and phantom reads here (more on phantom reads below).
Click through for the full list.
At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes:
- 0 = the VLF is not active (i.e. it can be (re)activated and overwritten)
- (1 = not used and no-one seems to remember what it used to mean)
- 2 = the VLF is active because at least one log record in it is ‘required’ by SQL Server for some reason (e.g. hasn’t been backed up by a log backup or scanned by replication)
A few weeks ago I learned about a new VLF status code that was added back in SQL Server 2012 but hasn’t come to light until recently (at least I’ve never encountered it in the wild). I went back-and-forth with a friend from Microsoft (Sean Gallardy, a PFE and MCM down in Tampa) who was able to dig around in the code to figure out when it’s used.
Read on to uncover the mysteries of the VLF status of 4.
I’m going to create a table and insert exactly 1 million rows. This particular table will be a clustered index, and will contain 1 million GUIDs.
12 CREATE TABLE dbo.demoNOLOCK (someguid uniqueidentifier NOT NULL PRIMARY KEY);INSERT dbo.demoNOLOCK (someguid) SELECT TOP (1000000) NEWID() FROM sys.all_columns t1, sys.all_columns t2;
Next I prove that there a million rows.
1 SELECT COUNT(*) FROM dbo.demoNOLOCK;
Now without inserting or deleting any rows, I’m going to shuffle them.
1 UPDATE dbo.demoNOLOCK SET someguid = NEWID();
And if while this is happening, I count the rows in a different session, I have to wait for that query to finish.
Read on to see what happens when someone gets the idea of running the select query with NOLOCK.
You can use Distributed transactions which specifies the start of a SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC). This is only applicable to SQL Server and no Azure. Transaction-level snapshot isolation does not support distributed transactions.
Is a user-defined transaction name used to track the distributed transaction within MS DTC utilities. transaction_name must conform to the rules for identifiers and must be <= 32 characters.
Is the name of a user-defined variable containing a transaction name used to track the distributed transaction within MS DTC utilities. The variable must be declared with a char, varchar, nchar, or nvarchar data type.
Jeanne also includes the important comment that nested transactions don’t really work the way you’d expect them to. I probably could have ended that last sentence early with, “don’t really work.”
If you do not enable implicit transactions, and you don’t start an explicit transaction, you are in the default “autocommit” mode.
This mode means that individual statements are automatically committed or rolled back as whole units. You can’t end up in a place where only half your statement is committed.
Our question is really about whether there are unseen problems with this default mode of autocommit for single-statement units of work.
By force of habit, I wrap data modification operations in an explicit transaction. They let me test my changes before committing and the time you’re most likely to spot an error seems to be right after hitting F5.
With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction.
I will show you what this looks like with an example. Please note the code will not work for you because I dropped the foreign key constraint within the person.person table just so I could show you the example.
Arun mentions that the serializable isolation level prevents phantom reads. So do the repeatable read and snapshot isolation levels.
The statement “There is zero logging when DURABILITY=SCHEMA_ONLY” is not factually correct, its more like a minimally logged operation. What is surprising is the fact that logged as advertised for the in-memory engine should result in far fewer log records than the equivalent workload for the legacy engine, clearly this is not the case in this particular example and something I need to dig into somewhat deeper. Also note that the version of SQL Server being used is SQL Server 2016 SP1 CU3, which should be stable. One final point, in order to make sure that fn_dblog and fn_dblog_xtp produced clean results for me each time, I took the quick and dirty option of re-creating my test database each time.
This post definitely ranks in the “Microsoft did this right” category.
What exactly is a distributed transaction? It’s one where the work needs to be completed in more than one database so data is kept in sync everywhere. For example, if you need to update data in Database A and in Database B, and they need to be kept in sync, that’s a distributed transaction. Database A and Database B can be in the same SQL Server instance, or they could be in different instances … or even in different data sources, such as Oracle or DB2. This whole shebang is often referred to as a cross-database transaction. DTC is based on the principle of a two phase commit – for the whole thing to get done, all the little bits need to be committed everywhere before claiming complete success. Otherwise stuff needs to be rolled back so that things stay in sync and all is right in the world.
Read on for Allan’s thoughts and guidance.
00.000 – Session A sets its isolation level to snapshot
00.001 – Session A explicitly begins a transaction with BEGIN TRAN
00.002 – Session A starts a WAITFOR command for 15 seconds
10.000 – Before the WAITFOR completes, Session B inserts rows into dbo.Table
15.001 – Session A starts a SELECT from dbo.Table, which returns the rows that Session B inserted
This seems wrong, because many of us commonly say things like, “in Snapshot Isolation level, all statements see data consistent with the beginning of the transaction.”
But in this case, Session B inserted the rows after Session A began its transaction using Snapshot Isolation level. So why did Session A see those rows?
Kendra explains the nuance well, so read the whole thing.
The other day I had to update some records, in Production. I’m a firm believer of using explicit transactions and double checking things before committing a transaction. This helps ensure things go as expected. This also allows me a way to rollback the changes if they don’t. It happens.
However, this means that I have to COMMIT said explicit transaction. And not go to lunch without doing so.
Can you see my mistake? I bet you can.
Fortunately, it sounds like it wasn’t a critical problem. If you want to check for open transactions, Jack Vamvas has a couple methods.