The madness of “exclusive” row locks
· Jun 24, 04:19 PM by Thijs Kroesbergen for Brokenwire.NET
Yesterday I discovered some really weird behavior of SQL Server. I had a case where I could read a record that was exclusively locked by someone else. Considering the word “exclusive” you would expect that when one transaction has an exclusive rowlock an other transaction would be unable to read that same row. But there is one specific case where this isn’t true. In that case it is possible to read a record that is locked exclusively by someone else.
It took me (together with a colleague) a lot of time to finally find out what was happening.
To reproduce this behavior you need a test-table with some random data in it.
CREATE TABLE [MyTable] ([Col1] bigint PRIMARY KEY CLUSTERED, [Col2] bigint) INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (1,10) INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (2,20) INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (3,30) INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (4,40) INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (5,50)
You can put this table in any database, as long as it doesn’t have snapshot isolation turned on. The recovery model for your database doesn’t matter.
Now let’s run some queries and see what happens. To be able to test this properly you should run two different session against this table. To be able to hold (and see) the locks that used you need to start a transaction and run some statements, but don’t complete the transaction (yet).
First in the first window of the Query Analyzer (which I’ll refer to as session 1) select one row from the table and request an exclusive rowlock on it with the XLOCK and ROWLOCK table hints.
session 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT Col1 FROM [MyTable] WITH (XLOCK, ROWLOCK) WHERE [Col1] = 3
To verify the locks that where put into place you can run sp_locks to check the locks that are granted to the connection (spid) that you used to perform the actions for session 1.
spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- -------------------------------- -------- ------ 56 21 69575286 1 PAG 1:41 IX GRANT 56 21 69575286 1 KEY (030075275214) X GRANT 56 21 69575286 0 TAB IX GRANT
As you can see there is an “X” (exclusive) lock on the first key of this table. (The other locks are “IX” (intentional)). Now let’s move to session 2 and see if we can we retrieve that record.
session 2:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT Col1 FROM [MyTable] WHERE [Col1] = 3
I did expected this statement to just “hang” while waiting for the record to come available. But I was surprised to see that the record could be retrieved without a problem. Also, sp_locks doesn’t show any additional locks for this statement, not even a shared lock!
If you rollback session 2 (to clear everything that might have happened) and retry it with the HOLDLOCK table hint you do get the expected behavior, because the select in session 2 now will wait for the transaction in session 1 to complete.
To understand what is happening here you should remember one of the rules of read committed data access. This rule says that you can read any row as long as it’s in a committed state. The row we’re trying to read here is in a “clean” state (it’s not marked “dirty” by the system). In this case the optimizer decides that it doesn’t hurt to retrieve it via the index without checking for locks. So your table doesn’t even need a primary key, a long as you have an index containing the requested data the rowlocks may be skipped at will.
So if the locked record has not changed and the data for requested columns is stored in an index and you are working from an READ COMMITTED isolation level then the exclusive lock is possibly not honored.
One possible workaround is to add a “HOLDLOCK” table hint to the select in session 2. Alternatively you can actually update the record to have it exclusively locked (and marked “dirty”) in session 1. The last possibility is to lock an entire page instead of just one row by using the PAGLOCK hint. Exclusive page locks do prevent all other readers for the rows in that page.
There’s a thread on http://social.msdn.microsoft.com/ posted by someone who has observed the same weird locking behavior. A Microsoft employee responded with:
Using XLOCK in SELECT statements will not prevent reads from happening. This is because SQL Server has a special optimization under read committed isolation level that checks if the row is dirty or not and ignores the xlock if the row has not changed. Since this is acceptable under the read committed isolation level semantics it is by design.
Perhaps the worst thing about all this is that this behavior cannot be found easily in the Books Online. A small note somewhere in the section about table hints would have be nice. There are some hints in KB324417 (which applies to Sql Server 2000). Combine all that with the fact that the optimizer may choose to do this at will, you’ll have a very hard to find bug in your sql code.
Conclusion:
It took me a lot of time to find out what was going on here. So remember kids: a SELECT with XLOCK and ROWLOCK hints doesn’t mean that you are the only one who can read those rows!
New feature: Webslice
· Apr 20, 03:31 PM by Thijs Kroesbergen for Brokenwire.NET
Brokenwire.NET now has it’s own webslice. You may wonder what a webslice is. Remember 1997 ? That’s when we had snippets of web pages on our active desktop! Well a web slice is just that, it’s a piece of information from a site inside a small window in your browser. Internet Explorer 8 is the only browser that supports these things right now.
When you have installed the slice on you browser’s favorites bar it will look like this:
So if you have IE8 you can click on the small link in the right sidebar on this site to add the Brokenwire.NET webslice to your own browser.
Creating such a slice is very easy, with some help of this blogpost about webslices I was able to do this in 10 minutes.
The basic HTML structure:
<html><head> <title>Page Title</title> </head><body> <div class="hslice" id="techologynews"> <h2 class="entry-title">IE 8 web slice</h2> <div class="entry-content"> <p><a href='#'>Link 1</a></p> <p><a href='#'>Link 2</a></p> <p><a href='#'>Link 3</a></p> </div></div> </body></html>
The basic rules:
- The WebSlice must use the class name hslice in the container.
- Each WebSlice must contain an ID in the container. This is how the WebSlice will be differentiated from others on the page.
- The WebSlice must have at least one entry-title element defined. This will be displayed both in the page and in the feed button that appear in the Favorites Bar when a user subscribes to the WebSlice.
- While not required, each WebSlice should contain at least one entry-content element. This is the information that will appear when the user selects the WebSlice from their Favorites Bar.
There is also an official Microsoft whitepaper about webslices.
I wonder if anybody is actually going to use this, but it was a nice thing to play with for a bit.
Enjoy!
Timer.Elapsed event – The Silent Killer
· Feb 26, 12:54 PM by Thijs Kroesbergen for Brokenwire.NET
Yesterday we ran into some issue where exceptions would disappear into a black hole. We had some code running within the Elapsed event of a System.Timers.Timer which seemed to crash without us being able to catch the exception in our global “unhandled exception” exception handler. It looked like we had a Ninja in our code somewhere, deadly and accurate.
So we finally figured out what was happening and I’ll explain it to you here.
To start with the code to reproduce this looks like this:
An example project can be downloaded here: SilentKiller.zip
static void Main(string[] args) { Console.WriteLine("Starting..."); AppDomain.CurrentDomain.UnhandledException += OnUnhandledException; System.Timers.Timer aTimer; aTimer = new Timer(1000); aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent); aTimer.Enabled = true; Console.WriteLine("Press the Enter key to exit the program."); Console.ReadLine(); } private static void OnTimedEvent(object source, ElapsedEventArgs e) { Console.WriteLine("The Elapsed event was raised at {0}", e.SignalTime); Console.WriteLine("Ninja attack!"); throw new ApplicationException("Ninja!"); Console.WriteLine("You didn't see this one coming"); } private static void OnUnhandledException(object sender, UnhandledExceptionEventArgs e) { Exception ex = (Exception)e.ExceptionObject; Console.WriteLine("Caught an exception: " + ex.Message); }
The problem here is that you’d expect the OnUnhandledException method to fire, but this doesn’t happen. It seems like the exception is silenced within the timer! It took us quite a while to find that out. But actually the MSDN documentation is accurate about this, and my colleague Paul was the first to catch this line:
In the .NET Framework version 2.0 and earlier, the Timer component catches and suppresses all exceptions thrown by event handlers for the Elapsed event. This behavior is subject to change in future releases of the .NET Framework.
So it’s not a bug, it’s a feature!
And when you peek inside the System.dll assembly from the .Net framework you’ll see the following code for the handling of the Elapsed event
private void MyTimerCallback(object state) { if (state == this.cookie) { if (!this.autoReset) { this.enabled = false; } FILE_TIME lpSystemTimeAsFileTime = new FILE_TIME(); GetSystemTimeAsFileTime(ref lpSystemTimeAsFileTime); ElapsedEventArgs e = new ElapsedEventArgs(lpSystemTimeAsFileTime.ftTimeLow, lpSystemTimeAsFileTime.ftTimeHigh); try { ElapsedEventHandler onIntervalElapsed = this.onIntervalElapsed; if (onIntervalElapsed != null) { if ((this.SynchronizingObject != null) && this.SynchronizingObject.InvokeRequired) { this.SynchronizingObject.BeginInvoke(onIntervalElapsed, new object[] { this, e }); } else { onIntervalElapsed(this, e); } } } catch { } } }
Now I wonder who had the guts to break the rule “Never (ever) use an empty catch”! And why did they do this?!
(This would be a nice question for a Microsoft exam ;). How many of you knew about this?)
Super-easy access to the Sysinternals tools
· Feb 18, 11:54 AM by Thijs Kroesbergen for Brokenwire.NET
Leon Meijer blogged about the "new" way to access the sysinternals tools back in May 2008.
Today I found out another cool trick. You can not only use the http://live.sysinternals.com/ "Open Directory" server to download the tools, you can also do the following: (Start, run, type:)
And presto! You now have access to a network share with easy and up-to-date access to the tools!
You can also map a drive letter to this share if you want to, just type:
net use * \\live.sysinternals.com\Tools
If you consider yourself an "IT-Professional" and you haven't seen or heard of any of the Sysinternals tools, shame on you! (But hey, it's never too late to learn, so go check them out!)
(This trick depends on the Windows ability to access a WebDAV share as a regular network share, so this won't work on systems where the client bit (the WebDAV mini-redirector) isn't installed (such as my Windows Server 2003)). On Windows Vista this works straight out of the box.
Another reason for some of you to leave the "antique" versions of windows behind and move on?
Be prepared: get the Christmas Lights
· Nov 5, 10:06 PM by Thijs Kroesbergen for Brokenwire.NET
A long, long time ago I wrote about that cool christmas lights application, written by an unknown author in ancient times.
Recently I took up the plan to re-write this application using the .NET framework (check out the new logo).
I used Scott Hanselman's "BabySmash" as an inspiration to get started, and I was able to write this in a matter of hours.
So as we are getting closer to the holiday season, this is the time to grab your copy of the cool new Christmas Lights for you desktop from the download page. Christmas will never be the same again once you've experienced this!
The application "works" as of right now (tray-icon, blinking lights around the borders) but there is still a lot of room for improvement.
Here is a (non complete) list of features that I wish for:
- Get better artwork for the bulbs
- Be able to mix several types and colors of bulbs
- Let the user choose different blinking patterns (plugins?)
- Prevent the application from starting more than once :-)
- Make the number of lights configurable
- Make the blinking speed configurable
- Tune the frame rate to the amount of CPU power available
- Make a cool page with statistics about the number of blinks produced by this app...
Help me extend this list (and motivate me to work on this) by leaving a comment!
In the meanwhile, have fun! Oh and btw, did I mention you can download the Christmas Lights?
(More about the experiences with ClickOnce, Framework 3.5 and WPF in upcoming blog posts. And once I've tidied the sources I'll put it them here too.)
Tip: Batch files with over 9 parameters
· Oct 14, 09:27 PM by Thijs Kroesbergen for Brokenwire.NET
This has been around for ages, but it saved a us lot of work today (once we figured this out)..
So when you are writing a batch file (.cmd file) to automate some stuff you might run in to the issue that you can only retrieve the command line parameters up to number 9, by using the %1 to %9 variables.
Example:
@echo off
SET ONE=%1
SET TWO=%2
SET THREE=%3
SET FOUR=%4
SET FIVE=%5
SET SIX=%6
SET SEVEN=%7
SET EIGHT=%8
SET NINE=%9
So what do you do when you need number ten and up? %10 and up don't exist! Solution: You use the "shift" command!
Every time you call SHIFT the parameters will move 1 position, so if you call it ten times...
SHIFT
SHIFT
SHIFT
SHIFT
SHIFT
SHIFT
SHIFT
SHIFT
SHIFT
then you can retrieve number 10 and up..
SET TEN=%1
SET ELEVEN=%2
SET TWELVE=%3
SET THIRTEEN=%4
SET FOURTEEN=%5
SET FIFTEEN=%6
SET SIXTEEN=%7
SET SEVENTEEN=%8
SET EIGHTEEN=%9
How about that! (Be honest, did you know this?)
