THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: What rows have been updated under snapshot isolation?

So here it is, the bits and bytes post I’ve been promising….

This post is not about Hekaton (in-memory OLTP).

This is about the snapshot-based isolation levels that were introduced in SQL 2005, which was called optimistic concurrency at the time. With the advent of Hekaton’s truly optimistic concurrency in SQL Server 2014, we’re not calling it optimistic concurrency anymore. Or maybe we calling optimistic concurrency for reads. True optimistic concurrency has no locks at all, and with disk-based tables (i.e. not memory-optimized tables), SNAPSHOT isolation level and Read Commit Snapshot use locks for data modification operations.

So DBSI (disk-based tables with one of the snapshot-based isolation levels) allows older committed data to be available by writing it to a part of tempdb called the version store.  There is a DMV that shows you the contents of the version store, and we’ll take a look at it shortly.

The code below will create a new database called test (dropping it first if it already exists). Of course, you could just use an existing database of your own, which is why I also test for the existence of the table. The table has all fixed length columns, mostly character, so it will be easy to recognized the row contents. After creating the table, and inserting one row, I get the page number for the row using the undocumented fn_physlocformatter function, and take a look at the row. 

USE master;
GO
IF db_id('test') IS NOT NULL
   DROP DATABASE test;
GO
CREATE DATABASE test;
GO
USE test;
GO
ALTER DATABASE test SET read_committed_snapshot OFF;
GO
IF object_id('test_table', 'U') IS NOT NULL DROP TABLE test_table;
GO
CREATE TABLE test_table
(
  Col1 char(5) NOT NULL,
  Col2 int     NOT NULL,
  Col3 char(3) NOT NULL,
  Col4 char(6) NOT NULL
);
GO
INSERT test_table VALUES ('ABCDE', 123, 'Old', 'CCCC');
GO
SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM test_table;
GO

Here is my result:

image

I then look at the page indicated, so you may have to replace the 292 with whatever you got as a page number.

DBCC TRACEON(3604);
DBCC PAGE(test, 1, 292, 1);
GO

Here’s my row contents:

image

It’s a bit out of scope for today to describe what the bits and bytes in the row actually mean. I’ve done it before, and every single bit is described in my SQL Server Internals book.

I enable RCSI (READ COMMITTED SNAPSHOT ISOLATION) for the database, then update the row and look at it again. I also take a look at the version store, before it can be cleaned out.

ALTER DATABASE test SET read_committed_snapshot ON;
GO
UPDATE test_table SET Col3 = 'New';
GO
DBCC PAGE(test, 1, 292, 1);
GO
SELECT * FROM sys.dm_tran_version_store;
GO

And here’s the new row:

image

Notice that the row is now longer. The first 25 bytes are the same, except for the  the 3 bytes that used to contain ‘Old’ (446c64) are now holding ‘New’ (4e6577). The new row is actually 14 bytes longer, which is the number of bytes that gets added to every row that gets involved with DBSI. There are 8 bytes for the transaction_sequence_number at which the row was updated, and 6 bytes for a pointer to a location in the version store where the previous version of the row can be found.

And in the version store I see this:

image

I’ve chopped down some of the column names, but if you run your own SELECT from the DMV, you can see the full names. The database ID is 10, so you can use the db_id() function to verify what database the row is from. The rowset_id is another name for the hobt_id, which is another name for partition_id. So once you get in the right database, you can verify the object with this query:

SELECT object_name(object_id)
FROM sys.partitions
WHERE partition_id = 72057594040549376;
GO

The metadata also shows the minimum row length and the actual row length (25 bytes).  And that used to be as far as I went. In class, I would just say that the last column shown was an encoding of the row data. But I had never looked at it closer. Until recently. And then I realized, as you may have done already because of the queries I’ve just run, that the record_image_first_part contains exactly the same byte string as the original version of the row. And that original version of the row is what was saved in the version store when the row was updated. So it’s really not some mysterious ‘encoding’ of the row data… it IS the row data.  So we can not only see the database and the object that the changes are from, but we can also see the rows that have been updated.

Be warned that the version store can become quite large, and it’s not always useful to be able to analyze every single row that has been updated. But sometimes, you just gotta know.

So now you do.

~Kalen

Published Friday, May 22, 2015 6:00 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement