Hey you! Yeah YOU! The you that’s reading this very text, I need you to do me a favor!
I’m collecting information for a blog post I’m in the middle of writing. I don’t want to spill the beans yet, but if you could take the time to answer the following questions, I’d appreciate it!
May 4, 2010
So let’s talk…
March 25, 2010
Transaction Phenomena – Part 2: The Non-Repeatable Read
SERIES: Concurrent Transaction Phenomena
In Books Online (BOL), Microsoft describes different kinds of transaction isolation levels in terms of phenomena that can occur during concurrent transactions. Specifically, they mention three kinds of phenomena: Dirty Reads, Non-Repeatable Reads, and Phantom Reads. You may have heard of these before, but correct me if I’m wrong, I just can’t find a good definition anywhere on BOL.
And that’s too bad, because these phenomena don’t just help describe the different transaction isolation levels. The phenomena actually define these levels.
These terms actually come from the ISO/ANSI standard for SQL-92. The terms themselves however do little to illuminate exactly the meaning of each. (What’s a phantom read?) When used by the ANSI/ISO standard, they mean something very specific and they’re key to understanding the different isolation levels.
In the next few days, I’d like to illustrate each phenomenon:
- Part 1: The Dirty Read (reading tentative data)
- Part 2: The Non-Repeatable Read (reading changed data)
- Part 3: The Phantom Read (reading new data)
- Part 4: Serializable vs. Snapshot
Part 2: The Non-Repeatable Read
The non-repeatable read occurs when a transaction re-reads a single record and finds that it has been changed or deleted.
From ISO/ANSI: Process P1 reads a row. Process P2 then modifies or deletes that rows and commits the change. If P1 rereads the row it receives the modified value or discovers the row has been deleted.
Changing Data
I like to think of Non-Repeatable Reads as being about reading data that has changed.
Changing Data
(I apologize in advance to my friends at work whose reaction I know will be “Boo”.)
But Non-Repeatable reads are not always a bad thing. In fact they’re often harmless or even required. When data has changed, you have to decide whether you want consistent data or whether you want current data. If you need to rely on consistent data, then a non-repeatable read is detrimental. Here is an example of a non-repeatable read that causes problems:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION IF EXISTS( SELECT 1 FROM Theatre.AvailableSeats WHERE seat = 'B23') BEGIN -- imagine a concurrent transaction -- sells seat B23 here and removes the record -- from table Theatre.AvailableSeats -- then no rows are returned here: SELECT price FROM Theatre.AvailableSeats WHERE seat = 'B23' -- and zero rows are affected here: DELETE FROM Theatre.AvailableSeats WHERE seat = 'B23' END COMMIT
Isolation Levels that Experience Non-Repeatable Reads
Just like dirty reads, this phenomenon is possible when using the READ UNCOMMITTED isolation level, but it is also possible when using the READ COMMITTED isolation level. And in SQL Server READ COMMITTED is the default isolation level for transactions.
My experience
To avoid this phenomena, you could use isolation level REPEATABLE READ. But often I find it’s possible to rewrite a transaction and stick with the default READ COMMITTED.
December 16, 2009
Gleeful Calamity
Uhhh Ooooo
“Gleeful Calamity”
… is a phrase that I’ve been giving thoughts to for the past few days. I’m quoting a phrase that Gever Tulley used in a TED talk he gave about his tinkering school.
In many situations it’s valuable to have the freedom to fail. Especially when the goal is learning.
When learning new things about SQL Server, I couldn’t take in half as much knowledge if I didn’t have a development server called sandbox. The sandbox db sits on my local machine. And like its namesake, it’s a safe place to play. It’s a place where starting from scratch from time to time is normal and expected. I expect that the tempdb fills this role for many of you.
Being Creative (Idea Mash-ups)
Having the freedom to fail is also valuable when you want to be creative. Later on in this post I’ll explain a failure of mine and I’ll go through what went wrong and why it doesn’t matter.
Being creative often entails tweaking an existing idea or taking two separate ideas and putting them together.
Sometimes the Idea Mash-ups Work…
- Deadlock graphs + blocked process reports = this idea
- transactions + db snapshots = this script
- TaunTaun + sleeping bag = fantastic-ness
… and Sometimes They Don’t
Here’s one failed idea I had. A long time ago, I work with a database that had 800 tables in one schema in one database. By comparison, the Adventureworks database has about 70 tables in five schemas. I asked myself. Is there an automatic way to group the tables into candidate schemas while staying agnostic about the business rules? Here was my thinking:
- I can infer table relationships using defined foreign keys, sys.sysdepends and other clues based on the naming conventions we used.
- Tables and their relationships remind me of vertices and edges in graph theory.
- I can rely on theories of dead mathematicians to do my work for me!
… in theory any way. In practice – as I found out – there’s no substitute for digging into the business rules of a domain, sigh. But the process was fun and didn’t take too much time. While I was investigating this idea, I thought this is what creativity feels like.
Meta-Calamity
When I started writing this post, I had a different example of a personal failure in mind:
In my sandbox, I once explored a (seemingly) novel idea and after a while, I decided it sucked. As it turns out, Google tells me that my idea was not so novel after all and was written up in a blog as a good idea by someone I respect. So for that and other reasons, I picked another failure. Lesson: Google seemingly novel ideas before beginning a blog topic about it.
(Image is courtesy of Anthony Cain on Flickr… Check out his photostream!)
December 1, 2009
Comparison: Switching Tables vs. sp_rename
Takeaway: Stick with sp_rename and leave the ALTER TABLE SWITCH statement for partitions.
Earlier in the month I was at a talk given by Don Vilen called To Partition or Not To Partition The questions .. and the answers. In the talk he gave an interesting tip about switching partitions. (Remember that switching partitions is a method that is used to implement the sliding window strategy. For more info, search for the keyword SWITCH in this page).
Here’s the tip: Notice that the partition arguments in the ALTER TABLE – SWITCH command are optional. This means that you can swap the data of any table with another. It doesn’t have to be partitioned. But that staging table must still be empty.
So as I thought about how to get around the empty staging table restriction, I had an idea (Great Scott!) This is the diagram I made in my notes:
Not a flux capacitor
Moments later, Don Vilen summed up this idea: “The staging table must be empty. But there’s nothing stopping you from taking a third table with data and switching that into the newly emptied table.” I’m paraphrasing. He said it much better than that. Maybe an example would help:
USE tempdb; CREATE TABLE primary_table (i INT PRIMARY KEY); CREATE TABLE staging_table_with_data (i INT PRIMARY KEY); CREATE TABLE empty_staging_table (i INT PRIMARY KEY); INSERT primary_table VALUES (1), (2), (3) INSERT staging_table_with_data VALUES (4), (5), (6) -- the following does not work: ALTER TABLE primary_table switch TO staging_table_with_data; /* this fails with: ALTER TABLE SWITCH statement failed. The target table 'tempdb.dbo.staging_table_with_data' must be empty. */ -- but this does work: ALTER TABLE primary_table switch TO empty_staging_table; ALTER TABLE staging_table_with_data switch TO primary_table;
When would you use this?
The original reason that the SWITCH statement was implemented was to help transfer data efficiently by using partition switching. But I think we can use this feature on whole non-partitioned tables in order to quickly transform data.
A great example is an update statement that applies to a large number of rows that has to be done quickly. Myself, I’ve come across a situation where we were converting local times to UTC times and needed speed.
Is this any better than sp_rename?
In the domain of non-partitioned tables, the answer is no, not at all. In fact the code above can probably be written like this:
USE tempdb; CREATE TABLE primary_table (i INT PRIMARY KEY); CREATE TABLE staging_table (i INT PRIMARY KEY); INSERT primary_table VALUES (1), (2), (3) INSERT staging_table VALUES (4), (5), (6) EXEC SP_RENAME @objname='primary_table', @newname = 'old_data', @objtype = 'OBJECT' EXEC SP_RENAME @objname='staging_table', @newname = 'primary_table', @objtype = 'OBJECT'
You probably have seen this kind of technique used by tools that generate scripts to change table definitions. In fact this is the only technique you can use to add or remove an identity column to a table.
So then what’s the difference between them? Why would you choose one method over the other. Hopefully you can use the rest of the article to make an informed decision.
Restrictions
When renaming tables using sp_rename, there are very few restrictions, in fact I’m pretty sure there are only four:
- Neither the primary or staging table can be temporary
- The table can’t be published (via replication)
- The new name can’t clash with an existing object name.
- Enforced dependencies – like schema-bound views - can’t exist.
In fact, aside from syntax errors, I’ve never ever seen sp_rename fail. However, when swapping non-partitioned tables using the SWITCH keyword there are more pitfalls to watch out for:
- The columns, types, indexes and constraints of the primary and staging tables must match.
- too many others to list
To see what could go wrong, read the manual here or check out the error list with:
--roughly 50 rows here: SELECT TEXT FROM sys.messages WHERE TEXT like '%ALTER TABLE SWITCH statement%' and TEXT not like '%partition%'
But the the best approach would be to test specific scenarios on a test machine. Just to find out what’s possible.
Benefits of one over the other
So if you’re still with me (and congratulations and thank you for bearing with me) you might get the distinct impression that sp_rename is the better choice. You’re probably right. But I’ll still try to sum up the benefits and drawbacks of each method here:
sp_rename:
- Almost never fails.
- Different table definitions are possible.
- Indexed views (which rely on schema-bound views) are not allowed.
- Foreign keys and indexes must be recreated before or after the rename.
SWITCH
- You don’t have to recreate indexed views. But the staging table must have matching views before the switch.
- Other foreign keys, indexes and constraints do not need to be redefined. But as before, the staging table must be prepared properly.
- Minimal or zero downtime.
- The big drawback is the rule that requires the staging and the primary tables to have matching definitions.
So, like I said in the beginning: For the most part, stick with sp_rename and leave the ALTER TABLE SWITCH statement for partitions.
November 17, 2009
October 26, 2009
SQL Snippets is my new favourite thing.
Within the last hour, Mladen Prajdić announced a new version of his popular SSMS Tools Pack version 1.7. The main new feature being SQL Snippets. This provides Management Studio an add-in that is very much like the code snippets in other IDEs everywhere.
Also within that last hour, I’ve installed and test-driven the new feature and it is officially my new favourite thing. I’ve got big plans for this feature. I’ve already translated my custom templates to it. In conjunction with templates (and Ctrl-Shift-M), I foresee great things for this feature.
Good job Mladen!
October 4, 2009
Spotlight on Brad Schulz Y'all
Every now and then, I come across an idea where I think “Man, I wish I thought of that”
Reading Brad Shulz’s blog post The Age-Old SELECT * vs. SELECT 1 Debate was one of those times. In that blog post, he explains how relevant the select expressions are when used inside an EXISTS’s subquery. By looking at the official docs here: http://technet.microsoft.com/en-us/library/ms188336.aspx you’ll learn enough to get by. But after reading Brad Shulz’s article, you’ll never forget.
Brad is relatively new to SQL Server. He spent much of his career so far focused on Visual FoxPro and was a Visual Foxpro MVP for years. SQL Server is a new focus for him and he’s already moderating the MSDN T-SQL forum. It won’t be long before he’ll be able to create test database servers with one hand tied behind his back (Interesting trick: Sit on your right hand and try typing “create test database servers”.)
His blog http://bradsruminations.blogspot.com/ deserves more exposure. I was first introduced to the articles he writes in September as he syndicated his blog at SQLServerPedia.
His style is sometimes hilarious, and funny most of the time, but always clever. I’m always surprised when SQL Server writers manage to keep their articles engaging because of the subject matter. Brad seems to keep up just fine.
Also Must-Read (if only for the laughs)
Brad recently agreed to answer a number of questions I had. (Huge thanks Brad for taking the time!)
Michael J. Swart: What skills in Visual FoxPro (VFP) transfer easily to SQL Server? (Any surprising ones?)
Brad Schulz: Just working with the concepts of databases, tables, columns, relations, and indexes for many years in VFP makes the transfer easy. And basic SQL syntax has been part of the VFP language since 1991, so that eliminates an enormous learning curve right away… We VFPers have been doing INSERT, UPDATE, DELETE, and SELECT for almost 20 years.
That being said, though, we would still do a lot of one-row-at-a-time processing, because it was easy and it was lightning fast. But the equivalent of that in SQL (i.e. cursor) is slow as molasses, and I’ve learned a lot of set-based techniques in SQL Server that I had never used before. Thank goodness that I started playing with T-SQL recently… I think I would have gone bananas if I had started with SQL2000 with no CROSS APPLY or CTE’s or window functions (i.e. the OVER clause). How did you guys stay sane before SQL2005 came out?
Michael J. Swart: A common story is that many SQL Server professionals got their start as “accidental DBAs”. How did you get your start? Or how did you come to choose SQL Server as a focus?
Brad Schulz: My story’s not of the accidental variety. I got a B.S. in Computer Science from USC (University of Southern California) in 1981. I actually started out as a Building Design major, which is a combination of Architecture and Civil Engineering, but I dropped the Architecture class about a month into my freshman year, because I could see that I had no drawing talent whatsoever. To fill that void, I ended up taking a FORTRAN class independent study with a Computer Science professor. I gobbled it up, and finished the class in about 2 weeks. The professor asked me to be a tutor for others taking the regular class. I changed my major to Computer Science just before my sophomore year.
When I graduated from college, I immediately started working as a float in my parents’ pool. But after a couple of months, a friend of the family recommended me to a pear-packing company in Kelseyville, California (I found out later they were the largest pear-packer in the state). They had a computer system that no longer worked and the software company that custom wrote it for them was out of business. To make a long story short, they hired me as a consultant (God knows why), and I read the manual on the programming language (a form of BASIC on an HP-250 machine) and ended up re-writing their entire system. From that point, I got work by word-of-mouth, and I’ve been an independent developer ever since.
I worked in dBase II in the early 1980’s, and then eventually moved to dBase III, Quicksilver, Clipper, and then FoxBase+ and FoxPro. I’ve found plenty of FoxPro work for more than 20 years, but it’s not going to last forever, so SQL Server seemed like the logical choice to move to.
Michael J. Swart: What motivates you to moderate the MSDN T-SQL Forum?
Brad Schulz: Back in the 1990’s I participated in the FoxForum on CompuServe, answering questions about FoxPro. I enjoyed helping people, and I learned a lot just by helping others, and I learned new ideas from experts who hung out at the forum. I wanted to duplicate that experience at the MSDN T-SQL Forum. I’ve learned an incredible amount from some very talented people there.
Michael J. Swart: What motivates you to blog?
Brad Schulz: For one thing, I wanted to get myself known a little more in the SQL community. Some of my blogs are humorous or tongue-in-cheek, and that’s just a release for the crazy creative side of me. But I also enjoy writing “how-to” and “discovery” articles… they force me to practice and experiment more with T-SQL. I wrote several articles for FoxTalk and FoxPro Advisor in the 1990’s, but the turnaround time took forever, from submission through the editing process, and then waiting months for eventual publication. In contrast, I like the immediate publication of blog posts and getting immediate feedback.
Michael J. Swart: What’s one thing you can say about yourself that would make the SQL Server crowd say “Nifty”?
Brad Schulz: What’s a word that means “stylish; smart; fine; excellent” and rhymes with “thrifty”? Okay, scratch that.
Let me see… I’m an adopted child whose birth-mother was German Catholic and who played the violin. I always knew I was adopted. My birth-mother had requested I be placed with a German Catholic family (which I was, though my mother was the Catholic and my father was the German). My parents knew about the violin, and figured there was musical talent in me, so they set me up with piano lessons when I was 5. I’ve been playing piano ever since (45 years). The weird, spooky part is that I asked for more detailed non-identifying information from the adoption agency when I was about 25 years old, and when I received it, I was floored when it said that my birth-mother worked in and had a talent for “data processing” (whatever that meant in the 1950’s). Talk about “nature” vs “nurture”… I think the “nature” argument wins hands-down!
I also have a passion for the stock market, which I discovered in 1998. You can read more about that at my (now-on-hiatus) website http://www.stockciphering.com/whatis.htm. The stock market is just one huge database of stocks and daily prices, isn’t it? And so I wrote a suite of Visual FoxPro (VFP) programs to calculate technical analysis figures for stocks and produce buy signals for them, and I created the website to try to ca
pitalize on it. The website never really set the world on fire, but in “eating my own dog food” and using the data for my own trading, I did very well… From Jan2003 to Mar2009, my IRA (where I do my trading) more than quadrupled in value, compared to the S&P500 which lost 25% of its value during that same period.
Michael J. Swart: Thanks Brad
September 16, 2009
What the devil?
The devil you know vs. the devil you don’t.
A good friend of my told me about an issue she recently came across. It seems that a particular group of admins implemented SQL Server database mirroring in order to implement a kind of relatively cheap high availability solution. It’s a useful strategy that’s discussed in a number of places. Here and here for example.
Well, something went wrong or I wouldn’t be writing right now. I’m not familiar with all of the details, but there was some wrinkles with the performance of the network and the database server became extremely slow. (despite what you might think, this really isn’t an article about database mirroring) It’s ironic that the very thing that was implemented to increase up-time was the cause of down-time.
From another perspective, this is a case of “The devil you know vs. The devil you don’t.” Here, they traded the devil they knew – that is, not having a warm standby server – with a devil they didn’t – in this case, production uptime now depended on network performance in an unexpected way.
Other examples of known vs. unknown devils:
- Known bugs in version 1.0 vs. Unknown introduced bugs in version 2.0
- Raid 0 vs. Misconfigured SANs
- Reliability of municipal power vs. Possibly fawlty UPS systems
- Add your own examples to the comments! (best two comments get a No-Prize – extra points for irony.)
I’ve actually witnessed the unknown devils above. And as a lover of irony, I smiled (tempered slightly by sympathy for those whose necks were on the line).
The devil is in the details
In the examples I’ve mentioned above, the unknown devils reared their heads because of exposure to an unforeseen risk. What can you do about it though? Well, what else is there to do? Get to know that unknown devil. Test, test, test with all the time and resources available to you. There really is no short-cut. And sometimes, with the right testing, if you’re thorough enough you can get it right…
God is in the details*
Getting it right means you get the high availability you were hoping for. The uninterupted power. Secure systems, the robust bug free software. Maybe even five nines.
I’ll leave you with one last devil you know vs. devil you don’t example. It’s a picture of me and my brother. Presumably you know me and not him. But which is the evil one? Increased difficulty: neither of us have a goatee. Unfair: the photos have been scrambled.
Who’s the evil one?
* — BTW, “God is in the details” is a quote by architect Mies van der Rohe. I’m in good company when quoting architects for this industry; you’ve heard of design patterns right?
August 7, 2009
Intellectual Property Issues on SQL Blogs.
Late last week I got an email from a guy preparing to give a Sql Server seminar. He asked whether it would be okay if he used a drawing I did in an earlier blog post (with attribution of course). I’m not sure I replied in time for the seminar.
So since I’m glad to let anyone use my stuff for training (some rights reserved), I thought I’d license my blog under creative commons. You can find the details at the bottom of this page (unless you’re reading through RSS).
It was pretty easy, in case you’re wondering, you can generate nifty html to license your stuff on CC’s site starting here.
I thought this post fitting considering the fact that some bloggers I follow (notably Jonathan Kehayias, and Brent Ozar) have talked about blog plagiarism lately. So I wanted to make it explicitly clear what’s okay with the content of this blog.
July 9, 2009
Top 10 SQL Server Connect Items by votes.
Taking a page from cracked.com or David Letterman. Everything seems to be easier to read if it’s in a top n list. I decided to search the Microsoft Connect site to list the most voted feedback items for SQL Server (past and present). Mostly to see what Microsoft has done with them. 10. 284192 Farsi Language Support: Closed (Fixed) Suggestion. It’s supported with SQL Server 2008. This is a win for Microsoft (and Persian developers). 9. 331220 Backup / Restore without index data Active Suggestion. It was opened March of 2008 and remains opened. It seems like a great suggestion to me. You sacrifice backup/restore speed for smaller space. The Microsoft response is less than satisfying. They’re saying: “That’s too haaaaaard” (I’m paraphrasing). 8. 125117 Need 64-bit JET provider for x64 box Closed (External) Suggestion. I think external means that this is not a SQL Server issue but a MS Office issue. I imagine this one is so popular because it’s a component to a method that allows importing data from Excel sheets into SQL Server (via SSIS). Again Microsoft’s response seems like “Thanks for your feedback but no” 7. 127219 CREATE OR REPLACE Active Suggestion. A suggestion that is in its fourth year. Do you remember School House Rock? Do you remember that bill is waiting to become a law? That must be what this connect issue is feeling. There are a lot of emotional reactions around this issue. Microsoft’s response to this issue is “We can’t implement everything!” (again I paraphrase). But this feature is certainly got to be a lot simpler than backups without index data (see #9 above). Also, Aaron Bertrand thinks that Microsoft is finally seriously considering it, so that’s promising. 6. 299296 Relax restriction that table parameters must be readonly when SPs call each other. Active Suggestion. Erland Sommarskog opened this one; he’s an MVP who wrote – in my opinion – the definitive guide to Arrays and Lists. So he knows what he’s talking about. It would be a great feature and enable a really dynamic way to move data around while keeping to set-based principles. The suggestion was opened 12/10/2007. It was created as a response to Katmai details that were coming out about the new table-valued parameters feature. As such, it was a bit too late to consider this issue for 2008. But Microsoft does say that they have it “firmly in our radar to address as part of the next release” (direct quote this time) 5. 254293 Allow literals and read only table data to be represented as enums Active Suggestion. I’m not sure what to think about this. It would allow syntax that has where clauses something like: WHERE PaymentType IN (Mastercard, Visa) The hardest part is to decide what to do with a collision of enum values and column names. Microsoft says there are some workarounds and they’ll consider it in the future. I don’t see the value in this feature. Well, I do, but not enough to get this issue on this top 10 list. Let me know what you think. 4. 124550 Bring back stored procedure debugger in SQL Server 2005 Closed (Fixed). Win for Microsoft (and debuggers)! You see that debugger that’s available in SQL 2008? You can thank this issue. 3. 124561 Make full scripting for tables as it is in SQL2000 Closed (Fixed). Call this a win for Microsoft. It is fixed, but many people were not satisfied with the fix. I think it’s just fine. There is a new Scripting page in Tools –> Options. On a related note, Paul Randal describes an obscure feature that lets you script data from tables here. 2. 341872 IntellliSense backward compatibility Closed (By Design) Suggestion. A disappointment for me and many others. I’ve blogged about this before. Again Microsoft’s message seems to be “Thanks for your feedback, but no, that’s too haaaaard.” I paraphrase. What gets most people is that this used to work in one of the Katmai CTPs but they disabled it for RTM. 1. 326575 Release Service Pack 3 for SQL Server 2005 Closed (Fixed). I’m not sure, but isn’t this one like petitioning the sun to come up? I think SP3 was going to happen with or without the issue.
