October 16, 2013 at 6:19 am
David Burrows (10/14/2013)
Nice question.Due to the amount of criticism of previous QOD of the phrasing of the Q'a and A's I am surprised that no one posted anything about the wording of the answer!
It is technically incorrect as records are inserted into the table, they do not remain after the rollback.
Since the question asks for the result of the batch (although there are several bacthes) the answer should be 'no rows returned' or '0 Row(s) affected'
Please note I am not criticising the question or answer, just making an observation.
I would disagree with the observation - being a pedantic type, I take the view that the records were inserted into a page in the server's buffer and may even have been copied to disc (twice: once in te log and once if the dirty page was forced out) but were never inserted into the database. Insertion into the database occurrs when (and if) the unnested transaction in whose domain the update , insert, or merge command took place is committed, not before. As I've often pointed out, people playing with NOLOCK (or equivalently with READ UNCOMMITTED) are accepting that they are reading data which is not yet in the database, and may indeed never get there, which is asking for trouble, while people who maintain even a read-committed level of isolation (which is still far from true transactional isolation) read only stuff that is actually in the database (which for many cases is good enough, true transactional isolation is not always needed).
edit: I see Hugo has already made the same point. I guess I should have read to the end of the discussion before chipping in. Now that I have read on, I can see that I'm quite horrified to see people claiming that Hugo is wrong, that these records really were inserted into the table, and using dirty read to support that nonsensical claim. The word "nonsensical" is justified if we are thinking in terms of a relational database; if on the other hand someone claims that the table is the set of data currently in the server's cache or in the data file regardless of what is in the logfile they are clearly talking about something which is buried in implementation detail and is no part of the definition of the database and that of course can change at the drop of a hat. Of course it would be an extremely difficult position to hold, since it would of course guarantee that if there were any dirty pages on the disc at the time of a server failure, or indeed any dirty pages in cache that were still clean on the disc, restart would be fun - after all, the claim that the table is not what is defined by the log records is a bit hard to sustain when you have the server merrily doing rollbacks and roll forwards on restart.
Tom
October 16, 2013 at 6:34 am
L' Eomot Inversé (10/16/2013)
David Burrows (10/14/2013)
Nice question.Due to the amount of criticism of previous QOD of the phrasing of the Q'a and A's I am surprised that no one posted anything about the wording of the answer!
It is technically incorrect as records are inserted into the table, they do not remain after the rollback.
Since the question asks for the result of the batch (although there are several bacthes) the answer should be 'no rows returned' or '0 Row(s) affected'
Please note I am not criticising the question or answer, just making an observation.
I would disagree with the observation - being a pedantic type, I take the view that the records were inserted into a page in the server's buffer and may even have been copied to disc (twice: once in te log and once if the dirty page was forced out) but were never inserted into the database. Insertion into the database occurrs when (and if) the unnested transaction in whose domain the update , insert, or merge command took place is committed, not before. As I've often pointed out, people playing with NOLOCK (or equivalently with READ UNCOMMITTED) are accepting that they are reading data which is not yet in the database, and may indeed never get there, which is asking for trouble, while people who maintain even a read-committed level of isolation (which is still far from true transactional isolation) read only stuff that is actually in the database (which for many cases is good enough, true transactional isolation is not always needed).
Now that is really being pendantic 😀
And continuing your premise then 'SELECT COUNT(*) FROM table' before the rollback will return the wrong result as the table does not contain any rows 😛
Far away is close at hand in the images of elsewhere.
Anon.
October 16, 2013 at 6:53 am
Bhuvnesh (10/16/2013)
But I made the wrong selection . beacuse i was thinking (and this was my understanding ..earlier) that once the commit has been fired, the following rollback doesnt work for commit transaction.like
begin tran
update
commit
rollback
Above rollback wont "UNDO" the update (this was my understanding earlier)
The thing is that commit doesn't always commit a transaction. At any point in time, the system variable @@trancount for your sql session has a value, which is zero or a positive integer; if it's a positive integer, BEGIN TRAN does nothing but increment it, but if it's zero BEGIN TRAN starts a transaction and increments @@trancount. COMMIT raises an error if it's zero; otherwise it decrements it; if the result of decrementing it is zero it commits the transaction, othewise it does nothing (other than that decrement). This makes it possible to write stored procedures that use explicit transactions without having to worry about calling them when there's already an open transaction. ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
Tom
October 16, 2013 at 6:58 am
L' Eomot Inversé (10/16/2013)
David Burrows (10/14/2013)
Nice question.Due to the amount of criticism of previous QOD of the phrasing of the Q'a and A's I am surprised that no one posted anything about the wording of the answer!
It is technically incorrect as records are inserted into the table, they do not remain after the rollback.
Since the question asks for the result of the batch (although there are several bacthes) the answer should be 'no rows returned' or '0 Row(s) affected'
Please note I am not criticising the question or answer, just making an observation.
I would disagree with the observation - being a pedantic type, I take the view that the records were inserted into a page in the server's buffer and may even have been copied to disc (twice: once in te log and once if the dirty page was forced out) but were never inserted into the database.
Which demonstrates how exact one has to be with one's use of terminology. I wouldn't have thought to make the distinction between inserting into a page and inserting into the database.
However, if records were inserted into a page, can one say they were inserted into the table? Is the page part of the table at all times or is it not part of the table whilst it contains uncommitted transactions, and if the latter what if it also contains committed transactions?
October 16, 2013 at 7:00 am
L' Eomot Inversé (10/16/2013)
Bhuvnesh (10/16/2013)
But I made the wrong selection . beacuse i was thinking (and this was my understanding ..earlier) that once the commit has been fired, the following rollback doesnt work for commit transaction.like
begin tran
update
commit
rollback
Above rollback wont "UNDO" the update (this was my understanding earlier)
The thing is that commit doesn't always commit a transaction. At any point in time, the system variable @@trancount for your sql session has a value, which is zero or a positive integer; if it's a positive integer, BEGIN TRAN does nothing but increment it, but if it's zero BEGIN TRAN starts a transaction and increments @@trancount. COMMIT raises an error if it's zero; otherwise it decrements it; if the result of decrementing it is zero it commits the transaction, othewise it does nothing (other than that decrement). This makes it possible to write stored procedures that use explicit transactions without having to worry about calling them when there's already an open transaction. ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
This is something you really need to be aware of if you're coming from an Oracle environment.
October 16, 2013 at 7:31 am
David Burrows (10/16/2013)
And continuing your premise then 'SELECT COUNT(*) FROM table' before the rollback will return the wrong result as the table does not contain any rows 😛
Actually reads within a transaction read the afterlooks of any rows previously written within that transaction; whether that's achieved by reading transaction log records or by reading dirty rows for which the reader owns exclusive locks is implementation detail, and doesn't affect what's in the table and what isn't.
Once (about 20 years ago) there was a working prototype relational database which allowed a read to choose whether to see the afterlook written by the transaction containing the read, or to see the current value (ie the beforelook); it had rather useful parallel scaling characteristics - adding servers to the network gave linear speedup on OLTP-type loads up to as many as we tried; but as the query language was not in the least SQL-like (nor even QUEL-like, although that was closer) it wasn't considered commercially viable so it never got out of the lab.
Tom
October 16, 2013 at 8:19 am
What would be some real-life situations in which you would use nested transactions?
October 16, 2013 at 8:23 am
marlon.seton (10/16/2013)
However, if records were inserted into a page, can one say they were inserted into the table? Is the page part of the table at all times or is it not part of the table whilst it contains uncommitted transactions, and if the latter what if it also contains committed transactions?
That's the right question to look at, I think. You also have to worry about the case where the page contains old versions of rows for which an updating transaction has committed - the updated page won't be in the server's cache if the server crashed and has been brought back up, so we certainly can't rely on that cache to give us the answer. The database recovery process which runs on every server start ensures that pages are fixed so that they represent the tables correctly by modifying them as required by the log (rolling forward or back as appropriate). So from that I think I have to conclude that the current state of the tables in the database is always represented by what's in the data file(s) as modified by the log records because if there were a time when it wasn't so represented there would be no possibility of recovery if the server crashed at that time. So it isn't what's in the pages in the datafile if there are log records that say it's different, and it can't be what's in the cache if there are log records that say it's different - and a log record for which there is no corresponding commit record in the log says clearly that what's in the datafile modified by the cache is not currently correct, if it didn't say that rollback as part of recovery on startup would not be a valid operation. So the current implementatin of SQL Server seems to imply that theory is correct, and the updates made by uncommited transactions are not in the database (because they are excluded by those log records).
Tom
October 16, 2013 at 8:27 am
L' Eomot Inversé (10/16/2013)
ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
So does it mean that it will also undo the work which has been committed in exact prevoius step (as mentioned in this post earlier) ,
but commit means hardened the data into disk. please clear ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 16, 2013 at 8:38 am
Bhuvnesh (10/16/2013)
L' Eomot Inversé (10/16/2013)
ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
So does it mean that it will also undo the work which has been committed in exact prevoius step (as mentioned in this post earlier) ,
but commit means hardened the data into disk. please clear ?
If you have this structure:
BEGIN tran
INSERT...
COMMIT tran
BEGIN tran
INSERT...
ROLLBACK tran
you will only roll back second transaction because the first COMMIT brought @@TRANCOUNT to 0 so the first transaction was written to the database.
However, with this structure:
BEGIN tran
INSERT...
BEGIN tran
INSERT...
COMMIT tran
ROLLBACK tran
Nothing is written to the database because the first COMMIT brought @@TRANCOUNT down from 2 to 1 and then the ROLLBACK rolled back everything.
Apologies if I've used the wrong terminology anywhere.
October 16, 2013 at 8:46 am
Bhuvnesh (10/16/2013)
L' Eomot Inversé (10/16/2013)
ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
So does it mean that it will also undo the work which has been committed in exact prevoius step (as mentioned in this post earlier) ,
but commit means hardened the data into disk. please clear ?
Maybe you could thoink about it this way:-
There are two different meanings to commit here; one (which we could call a "maybe commit") means "signal the end of something which was declared as a transaction and take appropriate action"; the "maybe commit" has to find out whether or not that something was a real transaction, and if it was do a real commit. A real commit hardens the updates (and inserts) to the log - it doesn't have to do any checking about whether the something declared as a transaction was a real transaction, because the only thing that ever calls the real commit is the maybe-commit which does teh checking for it. When transactions are nested, only the outermost one is a real transaction so only it can have a real commit. The inner ones are declared as transactions in the source language, but are not real transactions. If transactions are not nested, all maybe commits will call real commits. The only commit you can call from T-SQL is the maybe-commit, you can't call the real commit directly - in fact none of SQL Server's user interfaces exposes the real commit.
edit: Marlon's descrpition of this is correct too.
Tom
October 16, 2013 at 4:12 pm
L' Eomot Inversé (10/16/2013)
I'm quite horrified to see people claiming that Hugo is wrong, that these records really were inserted into the table, and using dirty read to support that nonsensical claim.
I think you're being too harsh here.
The QotD here is sometimes about the "outside behaviour" of the database; and at other times it is about internals. This is the distinction I made earlier in the discussion too. If you expect the question to focus on internals and implementation, you would be right by saying that the records are inserted and than later "uninserted" as a part of the rollback processing.
If both options had been in the answer list for this question, I'd have to take a 50% gamble. Because the question does not state if it describes internals or end effect.
October 16, 2013 at 4:25 pm
marcia.j.wilson (10/16/2013)
What would be some real-life situations in which you would use nested transactions?
I have not seen any practical use cases for direct nesting of transactions in a single code block. But nested transactions can often happen when working with procedures that call each other.
Imagine a bank. You'd probably have a stored procedure TransferFunds that takes three arguments (Amount, AccountFrom, AccountTo - and maybe EffectiveDate as a fourth). This stored procedure would of course do two UPDATE statements, one to credit AccountTo and one to debit AccountFrom - and these two statements have to fail or succeed as a unit, so they'll be enclosed in a transaction.
Now suppose you have to complete a complex financial transaction that involves various transfers between various accounts. For each of these transfers, you'd call the TransferFunds procedure. But the complex transaction in itself also has to either succeed as a whole, or fail as a whole - so you enclose your code in a transaction. And look - now the TransferFunds procedure is suddenly using a nested transaction.
When writing a stored procedure that has to use a transaction but can also be called from within a transaction (explicit or implicit), you need to know how nested transactions affect the behaviour of COMMIT and ROLLBACK. And when writing code that starts a transaction and then invokes procedures that may have their own transactions, you also need to understand what a COMMIT or ROLLBACK from one of those transactions will do.
In the example above, the SQL Server behavior is great. You don't want the COMMIT in the TransferFunds procedure to actually commit the changes if it's still possible that the whole of the complex financial transaction has to be rolled back. But there are definitely also situations where you would like a nested commit to actually really commit the changes and not undo them even if the outer transaction rolls back - the example I run into most often is logging of debug information that might help me troubleshoot an error; I can log all I want, the error will cause a ROLLBACK, and I will be looking at an empty logging table...
October 17, 2013 at 3:08 am
L' Eomot Inversé (10/16/2013)
Bhuvnesh (10/16/2013)
But I made the wrong selection . beacuse i was thinking (and this was my understanding ..earlier) that once the commit has been fired, the following rollback doesnt work for commit transaction.like
begin tran
update
commit
rollback
Above rollback wont "UNDO" the update (this was my understanding earlier)
The thing is that commit doesn't always commit a transaction. At any point in time, the system variable @@trancount for your sql session has a value, which is zero or a positive integer; if it's a positive integer, BEGIN TRAN does nothing but increment it, but if it's zero BEGIN TRAN starts a transaction and increments @@trancount. COMMIT raises an error if it's zero; otherwise it decrements it; if the result of decrementing it is zero it commits the transaction, othewise it does nothing (other than that decrement). This makes it possible to write stored procedures that use explicit transactions without having to worry about calling them when there's already an open transaction. ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
Thanks Tom for explanation.So it means that "Commit" is not playing any role here (above Tsql block) ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 17, 2013 at 3:56 am
Bhuvnesh (10/17/2013)
L' Eomot Inversé (10/16/2013)
Bhuvnesh (10/16/2013)
But I made the wrong selection . beacuse i was thinking (and this was my understanding ..earlier) that once the commit has been fired, the following rollback doesnt work for commit transaction.like
begin tran
update
commit
rollback
Above rollback wont "UNDO" the update (this was my understanding earlier)
The thing is that commit doesn't always commit a transaction. At any point in time, the system variable @@trancount for your sql session has a value, which is zero or a positive integer; if it's a positive integer, BEGIN TRAN does nothing but increment it, but if it's zero BEGIN TRAN starts a transaction and increments @@trancount. COMMIT raises an error if it's zero; otherwise it decrements it; if the result of decrementing it is zero it commits the transaction, othewise it does nothing (other than that decrement). This makes it possible to write stored procedures that use explicit transactions without having to worry about calling them when there's already an open transaction. ROLLBACK always (unless you specify an invalid transaction name) sets @@trancount to 0 and rolls back the transaction (unless @@trancount is already 0, in which case it raises an error. @@trancount is initialised to 0 when the session starts.
Thanks Tom for explanation.So it means that "Commit" is not playing any role here (above Tsql block) ?
COMMIT will work in above Tsql block. Next line will give error because number of BEGIN TRANS is not equal to number of COMMIT + ROLLBACK
Below code will give error but table will still be created :
begin tran
create table #hello(id int)
select * from #hello
commit
select * from #hello
rollback
select * from #hello
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply