March 10, 2015 at 9:49 pm
Hi,
I was wondering if anybody could quickly point me to a good beginner-level tutorial, preferably free and on this website, on transactions. For some reason, it seems nobody has made a stairway just on transactions.
Most TSQL I understand, but for some reason I'm having a hard time wrapping my head around the basics of "what happens when you use this transaction-type command vs. this other transaction-type command," and "when do you want to use transactions" and "what impact does using transactions have on a production database vs. not using transactions."
Thanks. 🙂
March 11, 2015 at 4:40 am
I just did a quick couple of searches on Simple-Talk, and I don't see any. I've bugged the editors to get you one. In the mean time, understanding how the transaction log works is a part of understanding how transactions work. Here's a good starter[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2015 at 5:31 am
I realize that this isn't an article on transactions as such, but it is about how they are used Handling Constraint Violations and Errors in SQL Server[/url] ( https://www.simple-talk.com/sql/t-sql-programming/handling-constraint-violations-and-errors-in-sql-server/ )
I quote ....
'Transactions enable you to keep a database consistent, even after an error. They underlie every SQL data manipulation in order to enforce atomicity and consistency. They also enforce isolation, in that they also provide the way of temporarily isolating a connection from others that are accessing the database at the same time whilst a single unit of work is done as one or more SQL Statements. Any temporary inconsistency of the data is visible only to the connection. A transaction is both a unit of work and a unit of recovery. Together with constraints, transactions are the best way of ensuring that the data stored within the database is consistent and error-free.'
etc...etc...
I agree that it would be great to have a Stairway. When I wrote that article I ended up being surprised how little I really had known about the topic.
See also Rob Sheldon's Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask[/url] https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/
Best wishes,
Phil Factor
March 11, 2015 at 7:33 am
To start with TRANSACTIONs you need to understand the basic ACID rules of a Database.
Then you can check with SQL Server Transaction in MS BOL: https://msdn.microsoft.com/en-IN/library/ms174377.aspx?f=255&MSPPError=-2147217396
Other resources:
- Intro: http://www.sqlteam.com/article/introduction-to-transactions
- Transaction Isolation Levels: https://technet.microsoft.com/en-us/library/ms189122%28v=sql.105%29.aspx
- Nested Transactions: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]
- YouTube: https://www.youtube.com/watch?v=VLc4ewu6lUI
March 11, 2015 at 12:44 pm
Thanks, all, for the recommendations... I'll be sure to check those out when I have a chance.
If a stairway ever does get created, I just wanted to give a quick example of the kinds of questions I have:
Unless the idea is to replace it with COMMIT and see what happens to the rest of the code when the transaction fails? But then how is it better than just commenting out the transaction? Wouldn't you risk forgetting to change a ROLLBACK to a COMMIT and having code run improperly as a result, with no clues as to what's happening?
So... that's an example of the kind of question I have. 🙂 One of the things that often bugs me about online documentation is that they talk in depth about what you can do, but completely neglect to talk about why you would want to...
March 11, 2015 at 1:01 pm
Katerine459 (3/11/2015)
Thanks, all, for the recommendations... I'll be sure to check those out when I have a chance.If a stairway ever does get created, I just wanted to give a quick example of the kinds of questions I have:
What is the point of ROLLBACK? That is, what does it do for you that other things don't do better? When you run a transaction with ROLLBACK, it tells you how many rows were affected, but it doesn't tell you which rows, so how is it not worse than using SELECT?Unless the idea is to replace it with COMMIT and see what happens to the rest of the code when the transaction fails? But then how is it better than just commenting out the transaction? Wouldn't you risk forgetting to change a ROLLBACK to a COMMIT and having code run improperly as a result, with no clues as to what's happening?
So... that's an example of the kind of question I have. 🙂 One of the things that often bugs me about online documentation is that they talk in depth about what you can do, but completely neglect to talk about why you would want to...
Ah, but here's a fun little factoid I've picked up around here:
Even if you don't explicitly wrap your query in a BEGIN TRANSACTION / COMMIT, it's still a(n implicit) transaction.
So if you try to run an UPDATE and it fails for some reason, it'll get rolled back. I'm not sure there's a way for a SELECT to rollback, as it's not changing anything...:-D
March 11, 2015 at 3:44 pm
jasona.work (3/11/2015)
Katerine459 (3/11/2015)
Thanks, all, for the recommendations... I'll be sure to check those out when I have a chance.If a stairway ever does get created, I just wanted to give a quick example of the kinds of questions I have:
What is the point of ROLLBACK? That is, what does it do for you that other things don't do better? When you run a transaction with ROLLBACK, it tells you how many rows were affected, but it doesn't tell you which rows, so how is it not worse than using SELECT?Unless the idea is to replace it with COMMIT and see what happens to the rest of the code when the transaction fails? But then how is it better than just commenting out the transaction? Wouldn't you risk forgetting to change a ROLLBACK to a COMMIT and having code run improperly as a result, with no clues as to what's happening?
So... that's an example of the kind of question I have. 🙂 One of the things that often bugs me about online documentation is that they talk in depth about what you can do, but completely neglect to talk about why you would want to...
Ah, but here's a fun little factoid I've picked up around here:
Even if you don't explicitly wrap your query in a BEGIN TRANSACTION / COMMIT, it's still a(n implicit) transaction.
So if you try to run an UPDATE and it fails for some reason, it'll get rolled back. I'm not sure there's a way for a SELECT to rollback, as it's not changing anything...:-D
Yep, all true. And you can't roll back something that just did a SELECT, as you said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2015 at 6:10 pm
jasona.work (3/11/2015)
Katerine459 (3/11/2015)
Thanks, all, for the recommendations... I'll be sure to check those out when I have a chance.If a stairway ever does get created, I just wanted to give a quick example of the kinds of questions I have:
What is the point of ROLLBACK? That is, what does it do for you that other things don't do better? When you run a transaction with ROLLBACK, it tells you how many rows were affected, but it doesn't tell you which rows, so how is it not worse than using SELECT?Unless the idea is to replace it with COMMIT and see what happens to the rest of the code when the transaction fails? But then how is it better than just commenting out the transaction? Wouldn't you risk forgetting to change a ROLLBACK to a COMMIT and having code run improperly as a result, with no clues as to what's happening?
So... that's an example of the kind of question I have. 🙂 One of the things that often bugs me about online documentation is that they talk in depth about what you can do, but completely neglect to talk about why you would want to...
Ah, but here's a fun little factoid I've picked up around here:
Even if you don't explicitly wrap your query in a BEGIN TRANSACTION / COMMIT, it's still a(n implicit) transaction.
So if you try to run an UPDATE and it fails for some reason, it'll get rolled back. I'm not sure there's a way for a SELECT to rollback, as it's not changing anything...:-D
But the real question is still the same; why encase something in BEGIN TRANSACTION... ROLLBACK? What do you get out of it? If you want to see the effect that your UPDATE or INSERT or DELETE will have, wouldn't you get far more useful results by replacing with a SELECT and just looking to see what rows will be affected? (That's what I meant about the SELECT; sorry I wasn't a little more clear).
And if the point is to see what happens to the rest of your code if one transaction in the procedure is rolled back, this seems to me like a risky way to do it.
The only benefit I can think of, is using ROLLBACK in error handling. But if that's primarily what it's used for, the BOL don't reflect that. In fact, this is the example code that the BOL give:
USE TempDB;
GO
CREATE TABLE ValueTable ([value] int)
GO
DECLARE @TransactionName varchar(20) = 'Transaction1';
--These statements start a named transaction,
--insert a two records, and then roll back
--the transaction named in the variable
--@TransactionName.
BEGIN TRAN @TransactionName
INSERT INTO ValueTable VALUES(1)
INSERT INTO ValueTable VALUES(2)
ROLLBACK TRAN @TransactionName
INSERT INTO ValueTable VALUES(3)
INSERT INTO ValueTable VALUES(4)
SELECT * FROM ValueTable
DROP TABLE ValueTable
--Results
--value
-------------
--3
--4
I look at this code, and my immediate -- and only -- thought is, "what is the point?" :rolleyes:
What I'd love to see is, say, ROLLBACK (and this is just an example question - this question applies to everything about transactions) used in an example that, in addition to showing how it's used, also shows why it's useful. :ermm:
March 11, 2015 at 6:32 pm
The BOL example is just that, a very basic example.
You actually have the right idea, you would use transactions (explicitly) as a way to error handle inside a query or stored procedure. As a hypothetical situation, if you have a stored procedure that does several inserts / update / delete steps, which don't rely on each other, you could wrap each in a transaction, and if it fails it rolls back the changes in the failed step and moves on the the rest.
You're also correct in that if you want to see what an update or delete might do, replace it with a select and go.
But I think you are to some extent possibly looking at transactions not quite correctly. You're thinking of them as a way to "see what might change," when they're really a "bail out" method when something runs into a problem so that you don't have a 1/2 done process.
OK, just re-read your last post, and you are on the right track for transactions, but I'm still leaving my previous paragraph because I think it's a pretty good description...
March 12, 2015 at 4:02 am
I like Jason's description. It's for undoing a situation.
So, it sounds like you see a lot of code of the variety like this:
BEGIN TRAN
UPDATE TableA
SET ColA = 42
WHERE ColB = 42;
--ROLLBACK TRAN
--COMMIT TRAN
And you're right. You could just run the SELECT to see how many ColB = 42 and if that's what you expect, run it. This approach is just a safety. Am I on the right server? Do I see the right number of rows? Am I in the right database? Are the stars in proper alignment? Great, highlight that COMMIT and finish this. No to any of those questions, highlight the ROLLBACK and bail. This is DBA paranoia at work. I tend to do it too.
But, that's not the critical use case. Instead it's in automated updates/deletes/inserts within stored procedures where you aren't there to make the checks. Instead, you also automate the checks, wrap the whole thing in a transaction (because, as was stated earlier, due to the ACID properties of the system, everything is in a transaction already) and COMMIT or ROLLBACK as dictated by the code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2015 at 4:35 am
As a counter-example of when you would wrap something in BEGIN TRAN ... ROLLBACK:
I do a lot of performance tuning. As such, I have to run procedures so that I can see their execution characteristics and execution plan, but I don't want any changes to be made to the database because I want to be able to run the procedure multiple times as part of the tuning process.
So:
BEGIN TRANSACTION
EXEC ProcedureToBeTuned
ROLLBACK TRANSACTION
I can run that as many times as I like and know that any data modifications that it makes will be undone, ensuring that future executions don't do different things or throw errors.
That's a special case though. Rollback's normally used with error handling. So the complex procedure that does 15 inserts, updates 8 tables and then deletes a pile of rows threw an error somewhere in the middle? Roll it back so that none of the changes are persistent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2015 at 4:53 am
How about these articles:
http://www.sqlservercentral.com/articles/Miscellaneous/allabouttransactionspart1/1583/
http://www.sqlservercentral.com/articles/SQL+Puzzles/allabouttransactionspart2/1586/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply