New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning

Continuing our series of live, online classes, Kimberly will be delivering her new IETLB: Immersion Event on Transactions, Locking, Blocking, Isolation, and Versioning in October! The class will be delivered live via WebEx on October 9-11 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end of the class.

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work pretty well for those in the Americas, Africa, and Europe. We also realize that this is complex content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

Here are some select quotes from prior attendees of Kimberly’s live, online classes:

  • “Kimberly is incredibly knowledgeable and was able to adapt the techniques to all the different scenarios presented to her.”
  • “The best educator I’ve ever seen. She makes complex concepts “magically” easy to grasp. Incred-amazing.”
  • “Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”
  • “This was REALLY good. Getting to an IE is tough there are only a few a year and more importantly because there are only a few they cover a pretty broad range of information. Since I do mainly database design and query tuning, I can’t justify much beyond IE1 as I don’t do day to day DBA work. Seeing you were offering the online event focused on a specific topic – very large tables – was PERFECT. I know I really need to improve my knowledge of the options in that area. I recalled the PV/PT architecture from IE1 and knew a refresher, coupled with the new information would be perfect. The cost was BEYOND reasonable. The time frame, at only about 1/2 a day, was easy to justify and easy to manage keeping up with regular work while I did it. So this worked out to be a perfect event.” – Todd Everett, USA
  • “Loved the online aspect. It felt like I was there with the question ability and having the questions just answered right there. I felt I had a voice and could ask anything and the ability to watch it later made it totally worth the registration.”
  • “I really enjoyed the ability to ask questions as the course went along so that I didn’t forget what I wanted to ask while you were teaching. This allowed for questions to come through and class to continue until a good stopping point to answer the questions. Plus having the questions written from other attendees was nice for future reference instead of trying to remember from an in-person class discussion.”

The modules covered will be:

  • Batches, Transactions, and Error Handling
  • The Anatomy of a Data Modification
  • Locking / Isolation
  • Table Maintenance and Schema Locks
  • Locking, Blocking, and an Intro to Deadlocks
  • Versioning

The price of the class is US$699 (or US$599 for prior attendees of any SQLskills class).

You can get all the details here.

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these so stay tuned for updates (and discounts through the newsletter).

We hope you can join us!

New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning

Continuing our series of live, online classes, Kimberly will be delivering her new IETLB: Immersion Event on Transactions, Locking, Blocking, Isolation, and Versioning in October! The class will be delivered live via WebEx on October 9-11 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end of the class.

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work pretty well for those in the Americas, Africa, and Europe. We also realize that this is complex content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

Here are some select quotes from prior attendees of Kimberly’s live, online classes:

  • “Kimberly is incredibly knowledgeable and was able to adapt the techniques to all the different scenarios presented to her.”
  • “The best educator I’ve ever seen. She makes complex concepts “magically” easy to grasp. Incred-amazing.”
  • “Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”
  • “This was REALLY good. Getting to an IE is tough there are only a few a year and more importantly because there are only a few they cover a pretty broad range of information. Since I do mainly database design and query tuning, I can’t justify much beyond IE1 as I don’t do day to day DBA work. Seeing you were offering the online event focused on a specific topic – very large tables – was PERFECT. I know I really need to improve my knowledge of the options in that area. I recalled the PV/PT architecture from IE1 and knew a refresher, coupled with the new information would be perfect. The cost was BEYOND reasonable. The time frame, at only about 1/2 a day, was easy to justify and easy to manage keeping up with regular work while I did it. So this worked out to be a perfect event.” – Todd Everett, USA
  • “Loved the online aspect. It felt like I was there with the question ability and having the questions just answered right there. I felt I had a voice and could ask anything and the ability to watch it later made it totally worth the registration.”
  • “I really enjoyed the ability to ask questions as the course went along so that I didn’t forget what I wanted to ask while you were teaching. This allowed for questions to come through and class to continue until a good stopping point to answer the questions. Plus having the questions written from other attendees was nice for future reference instead of trying to remember from an in-person class discussion.”

The modules covered will be:

  • Batches, Transactions, and Error Handling
  • The Anatomy of a Data Modification
  • Locking / Isolation
  • Table Maintenance and Schema Locks
  • Locking, Blocking, and an Intro to Deadlocks
  • Versioning

The price of the class is US$699 (or US$599 for prior live, online attendees) and there’s also a combo price for all three new classes announced today.

You can get all the details here.

The class was also announced in our newsletter today, with a US$100 discount for those people who received that newsletter, valid through the end of June. All future live, online classes will always feature a discount for newsletter subscribers.

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these so stay tuned for updates (and discounts through the newsletter).

We hope you can join us!

Low priority locking wait types

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.]

SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations require.

At the start of any online index operation, it acquires a S (share) table lock. This lock will be blocked until all transactions that are changing the table have committed, and while the lock is pending, it will block any transactions wanting to change the table in any way. The S lock is only held for a short amount of time, then dropped to an IS (Intent-Share) lock for the long duration of the operation. At the end of any online index operation, it acquires a SCH-M (schema modification) table lock, which you can think of as a super-exclusive lock. This lock will be blocked by any transaction accessing or changing the table, and while the lock is pending, it will block any transactions wanting to read or change the table in any way.

The new syntax allow you to specify how long the online index operation will wait for each of these locks, and what to do when the timeout expires (nothing: NONE, kill the online index operation: SELF, or kill the blockers of the online index operation: BLOCKERS – see Books Online for more info). While the online index operation is blocked, it shows a different lock wait type than we’re used to seeing, and any lock requests are allowed to essentially jump over the online index operation in the lock pending queues – i.e. the online index operation waits with lower priority than everything else on the system.

To demonstrate this, I’ve got a table called NonSparseDocRepository, with a clustered index called NonSparse_CL, and 100,000 rows in the table.

First, I’ll kick off an online index rebuild of the clustered index, specifying a 1 minute wait, and to kill itself of the wait times out:

ALTER INDEX [NonSparse_CL] ON [nonsparsedocrepository] REBUILD
WITH (FILLFACTOR = 70, ONLINE = ON (
	WAIT_AT_LOW_PRIORITY (
		MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)
	)
);
GO

I let it run for ten seconds or so, so make sure it got past the initial table S lock required. Now, in another connection, I’ll start a transaction that takes an IX table lock, which will block the final SCH-M lock the online index operation requires:

BEGIN TRAN;
GO

UPDATE [NonSparseDocRepository]
SET [c4] = '1'
WHERE [DocID] = 1;
GO

And then I’ll wait until the drive light on my laptop goes off, which lets me know that the online index rebuild is stalled. If I look in sys.dm_os_waiting_tasks (using the script in this post), I’ll see the rebuild is blocked (script output heavily edited for clarity and brevity):

session_id exec_context_id scheduler_id wait_duration_ms wait_type                blocking_session_id resource_description
57         0               4            7786             LCK_M_SCH_M_LOW_PRIORITY 58                  objectlock

Look at the wait type: LCK_M_SCH_M_LOW_PRIORITY. The _LOW_PRIORITY suffix indicates that this is a special lock wait attributable to the online index operation being blocked.

This also neatly proves that the wait-at-low-priority feature applies to both the blocking locks that online index operations require, even if the first one isn’t blocked.

And eventually the online index operation fails, as follows:

Msg 1222, Level 16, State 56, Line 1
Lock request time out period exceeded.

If I leave that open transaction in the other connection (holding its IX table lock), and try the index rebuild again, with the exact same syntax, it’s immediately blocked and the sys.dm_os_waiting_tasks script shows:

session_id exec_context_id scheduler_id wait_duration_ms wait_type                blocking_session_id resource_description
57         0               4            8026             LCK_M_S_LOW_PRIORITY     58                  objectlock

This shows that the initial blocking lock is blocked, and is waiting at low priority.

So if either of these wait types show up during your regular wait statistics analysis, now you know what’s causing them.