Friday, April 12, 2013

DBA Question of The Day: What is SAVE TRANSACTION?



 Hello Dear Reader!  I was teaching a class in Chicago last week and I got a lot of really great questions.  

We only had two days so time was short.  I got an email reminder today.  So this is the first blog that will answer some questions that I didn’t get to cover in depth in class, and I thought it would be an Excellent Question of the Day!  

The Question that I got was “What is the T-SQL SAVE TRANSACTION statement used for?”

The short answer is it is a way of setting a save point in multiple Nested Explicit Transactions.  For the Long answer we’ll look a little deeper.

REFRESHER
Back in a little over two years ago I wrote a blog, We Should Have A Talk: Your Database is Using ACID.  If you are unfamiliar with ACID go read up on it, because it is fundamental to what we are doing here.

In that same blog I talk about Explicit, Implicit, and Nested Transactions.  In case you miss the ACID reading let’s cover those.

Every Transaction in SQL Server is an Implicit Transaction, unless otherwise specified.  You can turn off Implicit Transactions but that’s a talk for another time.  Implicit means implied, rather than expressly stated. 

As with the picture we don’t have to say it.  Hopefully things end a little better for us.

 SQL Server works the same way.  No, not like a cat driving a car.  Transactions are just as Implicit.

Every single ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, or UPDATE is a going to be wrapped in a BEGIN TRANSACTION and end with either a COMMIT or a ROLLBACK.

Explicit means fully and clearly expressed or demonstrated; leaving nothing merely implied.



There is no mistake about it.  The following SQL Statement is an Explicit Transaction.

BEGIN TRANSACTION

     INSERT INTO dbo.myTable1 DEFAULT VALUES

COMMIT TRANSACTION

So we’ve covered Implicit and Explicit, now we get to NestedNested Transactions are multiple levels of Explicit Transactions.


For Example:



Here we have 3 levels.  However, if any one of them fail or ROLLBACK then they all Rollback.  

Some companies use Explicit Transactions as part of their error handling.  Nothing wrong with that, but wouldn’t it be nice if you could commit part of the statement, even if a later part fails?

SAVED!






Saved Transactions allow us to save our success as we go in Nested Transaction, so that a Rollback doesn’t wipe out all of our progress.  

Depending how your code works, you MIGHT be able to use this.  Test the logic very soundly.


So let’s look at how this works.  First we’ll create a Table to test out our transaction.

if exists(select name from sys.tables where name='myTable1')
begin
     drop table dbo.myTable1
end
go
create table dbo.myTable1(
          myid int identity(1,1)
          ,mychar char(50)
          ,mychar2 char(50)
          ,constraint pk_myid_mytable1 primary key clustered(myid)
          )

Now let’s create a transaction to insert some data and roll it all back.

BEGIN TRANSACTION
     INSERT INTO dbo.myTable1(mychar, mychar2)
     VALUES('some data 1', 'some more data 1')
     BEGIN TRANSACTION
          INSERT INTO dbo.myTable1(mychar, mychar2)
          VALUES('some data 2', 'some more data 2')
          BEGIN TRANSACTION
              UPDATE dbo.myTable1
              set mychar='some new data 1'
              WHERE myid=1
              BEGIN TRANSACTION
                   SELECT * FROM dbo.myTable1
              COMMIT TRANSACTION
          COMMIT TRANSACTION
     COMMIT TRANSACTION
ROLLBACK TRANSACTION

Here’s the Output from our SSMS Windows.



/*
OOPS did I say ROLLBACK!?
*/
SELECT * FROM dbo.myTable1



We had a ROLLBACK at the end.  So the work isn’t going to stick.  Sure enough another select from the table and everything is gone.  

Now let’s try the same code again, but now we’ll create a SAVE TRANSACTION and name is testSavePoint.  Note the syntax change.  *We need to call this in the ROLLBACK otherwise the whole thing will ROLLBACK again.*  ACID properties are still in play, so we need a COMMIT AFTER the ROLLBACK  
/*
Let's Try this again
*/
BEGIN TRANSACTION
     INSERT INTO dbo.myTable1(mychar, mychar2)
     VALUES('some data 1', 'some more data 1')
     SAVE TRANSACTION testSavePoint
     BEGIN TRANSACTION
          INSERT INTO dbo.myTable1(mychar, mychar2)
          VALUES('some data 2', 'some more data 2')
          save transaction testSavePoint
          BEGIN TRANSACTION
              UPDATE dbo.myTable1
              set mychar='some new data 1'
              WHERE myid=3
              BEGIN TRANSACTION
                   SELECT * FROM dbo.myTable1
              COMMIT TRANSACTION
          COMMIT TRANSACTION
     COMMIT TRANSACTION
ROLLBACK TRANSACTION testSavePoint
COMMIT TRANSACTION


Here’s our output from SSMS again.  This looks very similar to last time with only changes in the identity key.


/*
OOPS did I say ROLLBACK AGAIN!?

But did we save anything
*/
SELECT * FROM dbo.myTable1



This time however, only our Update Statement was rolled back.

“So Balls,” you say, “That’s cool, but will we actually use this?  If we are using Nested Transactions would we only want to save ½ of it?”

Excellent Question Dear Reader!  Let me answer that with a real world scenario.

I was working with some friends earlier this year.  We changed an update job that ran for hours from row-by-row logic to set based logic.  The result was minutes instead of hours.  There were two parts of this job that we worked on. 

It was a four step process.  First a range of data was selected from one table.  Second we looked for missing data and inserted anything that was missing based on matching natural keys.  Third we then looked at everything that matched and updated any rows that needed the information.  Fourth we updated the range selected in another table.  We needed all four steps to run.  A Saved Transaction wouldn’t work for us using their current logic.

However the job failed when we first ran it, because there was an error on saving the range table.  Insert and Updates worked just fine.  The job would look at the range table and try the same range.  Even though it was failing the job was reporting success because the error handling did not catch the rollback.  It was explicitly called, but executed on a GO TO on error statement.

While we would have still had to fix the error on the range table, we could have used SAVE TRANSACTION to save the insert and update.  Then moved the range table update to its own Transaction that would have better error handling.  It also would save retry operations on the job by not processing expensive Updates, Inserts, and Rollbacks.

Alright Dear Reader, there it is.  SAVE TRANSACTION and how it works with Explicit Transactions.  Thanks for the questions, keep them coming!

As always Thanks for stopping by.

Thanks,

Brad

Tuesday, April 9, 2013

T-SQL Tuesday #41 Becoming a SQL Server “Presenter” GET INVOLVED!




Hello Dear Reader!  It’s been a while since I participated in a T-SQL Tuesday, and this month Bob Pusateri (@SQLBob|Blog) is hosting it.    Bob is very active in the SQL Community, I met Bob briefly at the PASS Summit this year, and occasionally say Good Morning to him on Twitter.  His topic is one that really interested me, because it’s a story I’ve been meaning to tell but just hadn’t gotten around to.  Without further ado here’s Bob in his own words to describe the topic:

This month the prompt is how did you come to love presenting? What was the first time you gave a presentation in front of a group and really enjoyed it? Was it something that was required of you in school? Something you did in the workplace? Were you inspired by other SQL community members and thought “I think I can do that too”? Whatever your story is, I’d love to hear it. Not a presenter? Not a problem! Feel free to chime in with whatever you like that’s related to either presenting or SQL Server in general.

Sometimes I look at the things I’ve been able to do in my career and I wonder how did I get here?  First & foremost.  I have a tremendous support system.  Even to this day without the help of my ex-wife, friends within the SQL Community, and my co-workers (past and present) I wouldn’t be where I am today.  That support has helped more than I can say, which is probably why a lot of my blogs start with Thank You.  That support allows me to write the rest of this blog.

IN THE BEGINNING


Not so long ago, I didn’t know what a SSUG was.  Two years and six months ago (roughly), I attended my first SQL Saturday.    I did not have a blog.  I did not tweet.  If you would have asked me who SQLBalls was, I would have probably said, “That sounds like a serious medical condition you should have checked out.”  I had left the Office of the President and went to work for Publix.

Over the years I had moved the family a lot for my career.  This would be the move where we didn’t have to move anymore.  I had attended the first 24 hours of PASS, I posted on the forums at SQLServerCentral.com, but that was it.  Did I mention, I didn’t know what a SSUG was.  Didn’t know where my local one was, Kendal Van Dyke(@SQLDBA | Blog) was still putting together MAGICPASS.  An interesting thing had happened when I went to the 24 hours of PASS.  I saw a presentation on a subject I was pretty knowledgeable in.  There were several things that I had done that the speaker didn’t mention, and I wondered “did he know about them”?
I looked at the speakers like some secret Fraternity, I’m just a regular guy.  I had no idea how you got to speak at one of those things.  So I let it go.  While working for Pulix my buddy Dan Taylor (@DBABulldog| Blog) told me about SQL Saturday #49 Orlando.  If I liked the 24 Hours of PASS, then I’d love this.  So away I went.

“So Balls,” you say, “Who did you go see?”

Great question Dear Reader!  I started off strong.  My first live SQL Presentation ever was watching  Tom LaRock(@SQLRockstar | Blog) talk about Wait Stats, What Are You Waiting For? Next up Argenis Fernandez (@DBArgenis | Blog) Multi-Server Management with SQL Server 2008 +, then Nathan Heaivilin (@SQLHeaven ) Introduction to Execution Plans, Lunch on the Lawn where I met my friend Noel McKinney (@NoelMcKinney | Blog), next Jeff Garbus (LinkedIn) Choosing Indexes for Performance, Jorge Segarra(@SQLChicken | Blog) Policy-Based Management in a Nutshell, and Kevin Boles(@TheSQLGuru) SQL Server Partitioning from A-Z.

Yes I remember it like yesterday.  A fog had lifted.  I found people that not only understood what I did everyday but faced the same issues I did.  It was like a support group for DBA’s, only we have beers after our meetings instead of coffee and donuts (those are for SQL Saturday’s in the morning).

The after event was just as important.  I knew no one.  I was a stranger.  Even though I’m social I was quite nervous to just walk up to this gathering and invite myself in.  It is there that I met Jack Corbett (@UncleBiguns| Blog) ½ of my future Law Firm of Biguns & Balls.  Jack is a nice and welcoming guy.  We struck up a conversation immediately.  I found Noel right after that, Patrick Leblanc (@PatrickDBA | Blog), and Jack joined us and we talked for over an hour about nothing and everything.  I found my community that day.  And it was good.

THEN THERE WAS TAMPA


I wanted to get involved.  The next event that came around I wanted to submit for.  I figured I would get rejected, but I didn’t understand what SQL Saturday was for.  To grow local speakers, to give first time speaker’s a chance.  Little did I know my first shot was coming up.  I looked at what other speakers were doing and prepared.  Most had blogs, I set up the one you are reading now, got on Linkedin, Twitter.

The hardest part was coming up with the name I would use for Branding.  Several people in the community had written about that and I knew it was important.   It was a full days worth of work on the Saturday.  For the cost of the URL, signing up with Blogger, setting up a gmail account, and all the other stuff it was time well spent.  I’ve tweeked things over time, but it was quick and easy compared to the time it takes to blog J.

All of that underway I submitted to SQL Saturday 62 Tampa.  I don’t know that I’ve ever Thanked Pam Shaw(@PamShaw ) properly.  You see most of my presentations where not the final version I have now, and I tweek them constantly.  I find the more I present on a topic the more I learn about it.  She asked me for three topics, I gave her my three.  She picked the one topic that I didn’t expect.  Compression.  Whatever topic I present on, Compression will always be my first.  It set me on a very good path.

At the encouragement of Jack I submitted to SQL Rally.  I lost out on the voting but was a close second.  I got invited to present in a Wild Card spot.  I submitted to 5 more SQL Saturday’s and presented before the year was over.  I did 3 webinars, 2 on Compression (THANKS PAM!!!!), and on a whim submitted to the PASS Summit that year.

No way would I get selected.  It would be good experience.  I’d get rejected, I’d keep practicing, and next year I’d get my shot.  I submitted a Deep Dive that I had yet to finish writing.  Apparently they thought I was ready.  I got selected!

Me I’m still just a regular guy.  There are real stars out there in our community and they are awesome.  That’s not this story, Brent Ozar(@BrentO | Blog)  tells that one really good though, read Rock Stars, Normal People, and You by Brent.  It’s okay I’ll wait.  It’s worth the read.

WHATS YOUR BEGINNING


So that’s me.  That’s how I started out.  It doesn’t happen overnight.  It can happen quicker than you expected.  So where do you start?  Go to your local User Group.  Go to your local SQL Saturday.  Email a guy that works with a company that put’s on Webinars every Tuesday and Thursday at 11 am East Coast time.  *Cough* *Cough* <points to self> *Cough* *Cough*.

Tell me what you’ve been doing in the community, tell me where you’ve presented before.  If I’ve never met you have you done a webinar?  They aren’t always right for first time presenters, but give yourself a couple go’s and you’ll be ready.  Get in front of a crowd, let people ask you questions, take those questions and research them.  Write blogs, hop on forums, answer posts, research the ones you don’t know, and write blogs.  Sensing a trend?

Your story, if it hasn’t already started, can start here.  You Dear Reader have the ability to talk about something right now, that I would want to know about.  You have some insight that I would like to have.  The daily work you do gives you a perspective that no one else has.  That is valuable.  Your time, your work, your knowledge all make up who you are.

Every single one of us started somewhere.  If you’re well along that road Good Job, I hope it is paying off.  If you are just starting out, then you are in good company, because not all that long ago I didn’t even know what a SSUG was.

As always Thanks for stopping by.

Thanks,

Brad


Saturday, April 6, 2013

SQL Saturday 203 Boston, Deck and Demos Live!

Hello Dear Reader!

A quick note I'm presenting today at 1:30 pm for SQL Saturday Boston #203.

I'll be giving my presentation on the 4th Floor!  The topic today is SQL Internals, Backups, and Recovery Models!  OH MY!

Here's a quick link to the deck and the demo's, all live on the Resources page.

Also I'll be giving away a seat or two to the Pragmatic Works Performance Tuning Workshop here in Boston in June this year!  Make sure to stop by!

Hope to see you there!

Thanks,

Brad