Kalen Delaney : snapshot isolation

Geek City: Growing Rows with Snapshot Isolation

Kalen Delaney — Sun, 20 Nov 2011 00:43:00 GMT

I just finished a wonderful week in Stockholm, teaching a class for Cornerstone Education. We had 19 SQL Server enthusiasts, all eager to find out everything they could about SQL Server Internals. One questions came up on Thursday that I wasn’t sure of...(read more)

Did You Know? What's the Capital of Idaho?

Kalen Delaney — Tue, 01 Jul 2008 02:37:00 GMT

Can you say it out loud? You might have known the capital was Boise, but if you pronounced it Boy-zee, you were wrong. I was there last Wednesday for the kickoff meeting of the Boise SQL Server User Group, and was told that if I didn't pronounce the name correctly, everyone would know immediately that I wasn't from around there. The correct pronunciation is Boy-sea, or as I was spelling it when writing to my friend there: Boycee.

We had over 30 people show up for my presentation, in which I compared the two different concurrency models that SQL Server 2005 has available. I didn't dive into a really deep level discussion about the internals of locking and snapshot isolation, because there were people there with all different experience levels. I tried to stick to the behavioral differences and the costs involved with both choices.

It was a very enthusiastic group, with lots of good questions. There was also lots of good food, much than we could eat! We had about 20 pizzas, 3 coolers full of cold drinks, several bags of chips, and also several bags of cookies.

I get a lot of requests to come talk at User Groups, and usually I am open to it when I am in town teaching a class. This was the first time that I actually traveled by plane to go somewhere just to give a free User Group talk, and I did it because my good friend Cindy Gross, who works for Microsoft in Boise, asked me to. And Cindy made it well worth my while, giving me a wonderful guided tour of the Idaho capital, and a fabulous breakfast on Thursday!

Hopefully, I'll make it back again and Cindy has promised to take me rafting down the river. That sounds great, if I get back before the Winter!

~Kalen

Geek City: How SQL Server Detects the Correct Versioned Rows

Kalen Delaney — Thu, 03 Apr 2008 19:16:00 GMT

Here is a question I just received from the feedback page on my web site:

I have finished the book <the storage engine> and like it very much. I am now reading <query tuning and optimization>
I know in the READ COMMITTED SNAPSHOT isolation, when a row is being modified in a transaction, it generates an old committed version so another transaction can read it without being blocked.
But I don’t know how SQL Server uses SNAPSHOT isolation to prevent Phantom Read being happening.
In SERIALIZABLE isolation the ranged key or the whole table is locked, but in SNAPSHOT isolation, it can NOT generate any committed version since the row does NOT exist at the moment. So how does it know that the newly inserted data should not be included in the second SELECT statement?

-Tom

Tom is correct in that INSERTS do not generate versioned rows. However, SQL Server is able to keep track of when each change was made under snapshot isolation. Once a database is enabled for snapshots, every rows inserted, deleted or modified gets an additional 14 bytes of overhead added to it. These bytes contain 2 pieces of information. First is a row pointer to the previous committed version of the row, which is stored in the version store in tempdb. This pointer is only used for deleted and updated rows, since there will be no previous values for newly inserted rows.

However, these extra bytes also include a value called XSN, or transaction sequence number, which you can think of like a timestamp for a database. Any database enabled for snapshot keeps an internal XSN value, that is incremented each time any change is made, or any snapshot select is performed. The metadata also keeps track of all active transactions, and what the XSN was when the transaction started. It uses the view sys.dm_tran_active_snapshot_database_transactions for this. So when you are reading data, SQL Server will look at the XSN number in each row, and not return any rows that have an XSN value greater than the XSN value at the time the transaction started.

I hope this helps!

~Kalen

www.InsideSQLServer.com
www.SQLServerDVD.com

Geek City: Accessing auxiliary tables during Data Modification

Kalen Delaney — Tue, 22 May 2007 16:59:00 GMT

After reading Hugo’s post about when snapshot isolation doesn’t really live up to its promise, I decided to do some research of my own. I met with Stefano Stefani, one of the engineers at Microsoft on the storage engine team and we talked about what kinds of locks are taken during the data modification operations. We know that when a row is actually updated, SQL Server will take an Exclusive (X) lock, but there’s a lot more to the story than that.

First of all, you need to be aware of the fact that almost every data modification operation has to do some read operations in order to find the rows to be modified. So what kinds of locks are taken on the data while we’re reading it and checking to see if it is the data that needs to be updated?

In addition, reads may need to be performed on other tables, other than the table being modified. These ‘auxiliary’ tables can need to be referenced for two different reasons. First, if your query explicitly mentions additional tables in the FROM clause of your data modification statement, these tables are referred to as explicit auxiliary tables. There may be other tables that need to be referenced because of a need to validate foreign key constraints. The tables that need to be accessed to validate referential integrity as called implicit auxiliary tables. What kinds of locks need to be taken on these auxiliary tables?

There are a couple of additional terms I need to mention that relate to how an operator in a query plan is processed in an execution plan. An operator in a SQL Server execution plan (e.g. scan, seek, join, etc) consumes input rows and produces output rows. For example, a seek consumes rows from an index on a particular column, and produces rows that that can meet certain conditions in the indexed columns. A join consumes rows from two different input sources and produces output rows that match either other on the join condition. Every operator can be classified as either blocking or non-blocking. An operator that consumes the input rows and produces output rows at the same time is referred to as non-blocking, or flow-through. Other operators need to consume all input rows before producing any output rows and are referred to as blocking operators, or stop-and-go operators. A common example of a blocking operator is sort, which has to ‘stop’ to sort all the input rows before it produces any output. A loop join operator on the other hand is non blocking, because for every pair of row it consumes from the two input, it determines whether there is an output row to be returned, or whether it should just go on and get the next row. You can get more details and examples about blocking and non-blocking operators in Craig Freedman’s excellent blog here:

http://blogs.msdn.com/craigfr/archive/2006/06/19/637048.aspx

All data modification query plans can be thought of as broken into two parts: what comes before the actual modification of the target table and what comes after. In a graphical plan, the before part is to the right of the table or clustered index modification. And the after part is to the left.

Read operations performed during the part of the plan before the actual target table modification will lock any auxiliary tables under the user requested isolation level of the session or query, and the target table will take U locks. User requested tables will be those that occur in FROM clause of the operation, i.e. explicitly referenced by the user. Here is an example from the pubs database, which you can download from here.

First create copies of the publishers and titles tables:

USE pubs

SELECT * INTO newpublishers

FROM publishers

SELECT * INTO newtitles

FROM titles

ALTER TABLE newpublishers ADD CONSTRAINT UPKCL_newpubind PRIMARY KEY CLUSTERED

(pub_id ASC)

ALTER TABLE newtitles ADD CONSTRAINT UPKCL_newtitleidind PRIMARY KEY CLUSTERED

(title_id)

ALTER TABLE newtitles WITH CHECK ADD FOREIGN KEY(pub_id)

REFERENCES newpublishers([pub_id])

Here is a SELECT that uses an explicitly referenced auxiliary table, followed by the graphical query plan. The target table is newtitles and the auxiliary table is newpublishers.

-- explicit

UPDATE newtitles

SET price = price * 1.1

FROM newtitles nt JOIN newpublishers np

ON nt.pub_id = np.pub_id

WHERE state = 'CA'

I have circled the update to the newtitles table itself. The lookup into the newpublishers table is performed before (to the right of) the update to the newtitles table. I realize the details in the plan might be too small to read, but the point is not the details here, but rather to show you the ‘shape’ of the plan, with auxiliary table referenced before the target table. If you want the details, you can run the query yourself, and observe the graphical showplan on your own system.

The part of the plan after the actual target table modification can involve additional tables not mentioned explicitly by the user. These are primarily tables used for PK/FK constraint validation. For these implicit auxiliary tables, the isolation level set in the session or by hints in the query is ignored; each implicit auxiliary table is accessed under read committed isolation and will acquire either U or S locks, and with different granularity (usually either row/key locks or range locks) based on the available indexes. The goal of the query processor is to prevent data corruption.

Here is a SELECT that uses an implicitly referenced auxiliary table, followed by the graphical query plan. The target table is again newtitles and the auxiliary table is again newpublishers.

-- implicit

UPDATE newtitles

SET title_id = title_id + '_$'

WHERE price > 20

For this query plan, note that the seek into the auxiliary table happens after (to the left of) the update of the target table. (Also note that you can’t actually execute this query, as it will fail due to string truncation.)

The internal locks for the extra scans required for these implicit tables are kept for the duration of the scan if the operator is a stop-and-go operator, like sort or hash where all the rows must be read before processing can begin. For non stop-and-go (i.e. leaf level operators or flow-through), the lifetime of the lock is like a normal read committed shared lock, with which the lock is released as soon as the data has been read.

If the isolation level is SNAPSHOT, normally these locks on the implicit tables will not cause blocking or be blocked. But SQL Server can detect if relevant rows have changed since the transaction started, and will then block. This gets back to the subject of Hugo’s post, referenced at the beginning of my post.

You can probably guess that you can have both implicit and explicit auxiliary tables in the same plan. Here is a query and a graphical plan that illustrates this.

So this final SELECT uses both an implicitly referenced auxiliary table and an explicitly referenced auxiliary table, followed by the graphical query plan. The target table is again newtitles and the auxiliary tables are newpublishers.

-- explicit and implicit

UPDATE newtitles

SET title_id = title_id + 'CA'

FROM newtitles nt JOIN newpublishers np

ON nt.pub_id = np.pub_id

WHERE state = 'CA'

In my next post, I’ll give you some query examples to illustrate SQL Server detecting changed data under SNAPSHOT isolation.

-- Kalen