Showing posts with label ACID. Show all posts
Showing posts with label ACID. Show all posts

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

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

Tuesday, March 29, 2011

We Should Have A Talk: Your Database is Using ACID

I feel like a lot of my life as a DBA has been lived in reverse.  I’m started out my DBA life as an involuntary DBA.  My first real DBA issue was when my company’s production website was crashing.  The Database server was tossing errors that the Log file was full, as a matter of fact it had filled up the entire hard drive it was on.  Looking back at it now it seems like such a simple problem. 

One of the issues I’ve had with learning everything in reverse is I often find things and think “Why didn’t anyone tell me this!?”  There is so much to learn with being a DBA, Fundamentals, Internals, Troubleshooting, Monitoring, and Functionality of so many versions.   I was a certified MCITP for 2005, before the first time a guy named Paul Randal (blog|twitter) mentioned ACID in one of his blogs and I said, hey what’s that?  Chances are you’ve see this mentioned, but this is something we all need to know about.

So file this under Fundamentals.  Your Database is using, and while normally we’d say “Drugs are Bad m’kay”, we’re going with a little ACID is a good thing.

“But Balls”, you say, “ACID, Transactions, Fundementals why should I care about this, how is this going to help me impress my boss, or help me manage my SQL Servers?”

Well Dear Reader, I’m really glad you asked.  I’m of the firm belief that the more you know about how a database is supposed to work, the more you understand what it does.  The concepts that we will discuss Stretch across SQL Server. 

TRANSACTIONS

You’ve probably heard the term Transaction before.  Everything that occurs in SQL Server is a Transaction, the essential process of how data flows from end to end in SQL Server. In the 1970’s a very smart man named Jim Gray started working on theories about how a reliable transactional system should work.  He is literally the father of transactional databases, and he did quite a bit of work for Microsoft in helping to develop SQL Server. Jim Gray and Andreas Reuter wrote a book called Transaction processing: concepts and techniques,   this is the definitive book for explaining Transacactions, get a free preview from Google Books.

 In SQL Server you can start a statement by saying BEGIN TRANSACTION, and you can finish it by saying COMMIT or ROLLBACK.

Think of a Transaction like driving a car on the highway.  The Transaction is the car, and the highway is our SQL Server Instance.  The destination is our database.  Either we get there, think COMMIT, or we turn around and go back home without reaching our destination, think ROLLBACK.

There are two kinds of Transactions, Implicit and Explicit.  And Implicit Transaction is an implied Transaction.  SQL Server will always wrap a BEGIN and a COMMIT or ROLLBACK around a transaction.  Here’s a quick example, let’s say you type:

SELECT
    *
FROM
    dbo.myTable1

What you don’t see is that when SQL takes your command, it reads it as:

BEGIN TRANSACTION

SELECT
    *
FROM
    dbo.myTable1
   
COMMIT TRANSACTION

The BEGIN and the COMMIT are Implied, if you type them out then they are Explicit.  An Explicit Transaction is an unambiguous transaction.  We are specifying the beginning and the ending.


ACID

The term ACID was coined by Theo Haerder and Andreas Reuter in the academic paper Principles of Transaction Oriented Database Recovery.   ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability.  And it describes the way that a Transaction, behaves.   Here is a link to the MSDN article on transactions, http://msdn.microsoft.com/en-us/library/ms190612.aspx.  All of the definitions are from the MSDN article because these are the terms you will want to know, defined in the way you would want to understand them for SQL.

ATOMICITY
                A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.”

CONSISTENCY
                When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.”

ISOLATION
                Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.”

DURABILITY
                After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.”

"But Balls", you say, "High level Balls, tell me from a High Level what does this acronym effect something I can see within SQL Server?"

Well Dear Reader, you see Atomicity in the way transactions behave as a unit of work.  Remember all of our work must be done or none of it.  This is accomplished by how our Transactions behave.  A wonderful example of Atomicity is the COMMIT or ROLLBACK operators that we discussed earlier.

There are two forms of Consistency that I can think of as examples.   First we can use Referential Integrity and Constraints in order to guarantee that a data field is not dropped that is the parent to child data.  You have a Datafile and a Transaction Log that make up your basic database files.  These are our physical file structures.  But we have internal file structures as well.  Internally when SQL Server writes or deletes a record, SQL makes sure that all pointers that would lead to that record are updates as well.  This ensures that we always have a Consistent view of our data accessible to our Users.

A good example of Isolation is the way SQL Server uses Locks.  In SQL Server some of our most common Locks are the Shared Lock and the Exclusive Lock.  By default in SQL Server’s Transaction Isolation Level, is READ COMMITED.  This means that if I have a simple table like below:

ID
Product
Description
1
Bike
Huffy 28 in Bike
2
Light Saber
Star Wars Toy

And let’s pretend that there are a couple hundred rows.  Let’s give a simple example if I go to Delete or Update the Light Saber row, and a millisecond after my Transaction starts, someone else tries to Select the record, what would happen?  My Update would find my record with a Shared Lock, and then it would change to an Exclusive Lock on the record when I begin to Update my data.  The Select statement would attempt to take a Shared Lock on the same record and it would be Blocked, having to wait until my Update finished.

Now all of this would only take a fraction of a second, but it is Locking and Blocking function as intended.  This is a very basic level and we’ll cover Transaction Isolation Levels at a later date.

The best example I can think of for Durability is Recovery.  Durability means that you have an advanced logging mechanism that allows a Transaction that is COMMITED to persist, even if the computer is shut down at the second a COMMIT is received.  Likewise it would ROLLBACK any Transactions that may have been in progress, or In-Flight, that had not yet reached a COMMITTED state, ensuring that our database is Durable through unexpected shutdowns.    To see Recovery in Action open up your SQL Server Instance, go to Management, SQL Server Logs, and look at when your server came online. 



You’ll find that when the databases where brought online there is a record for Transactions Rolled Forward and for Transactions Rolled Back, and you will see the message “Recovery Complete” for databases.

We touched on a lot of topics today that each could be and deserve their own blog post if not series, Transaction Isolation Levels, Locking & Blocking, Internal File Structures, Logical File Structures, and Recovery.  To be honest we could have continued on for much longer, but what I want you to see is that ACID is fundamental to your database. 

So Dear Reader, I hope you can use this going forward.

Thanks,

Brad