Jump to content
Urch Forums

Simple transaction question


AlbaLed

Recommended Posts

transaction is treated as a unit of work to be done in order to accomplish a task. Since its a most basic unit of work that just do the work for which it is created we cannot nest any transaction in it.

 

it can be treated as a single computer operation performed by cpu.

 

The most basic example is data transaction between 2 entities, here sole purpose is to transfer data frm source to destination. once the data is transferred transaction completes.

 

But if you look frm broader percpective, it is a complete program or file being transferred but that is occuring thru different transactions tht start and finish delivering fragments of data between 2 entities.

Link to comment
Share on other sites

Originally posted by Vinay

 

transaction is treated as a unit of work to be done in order to accomplish a task. Since its a most basic unit of work that just do the work for which it is created we cannot nest any transaction in it.

 

it can be treated as a single computer operation performed by cpu.

 

The most basic example is data transaction between 2 entities, here sole purpose is to transfer data frm source to destination. once the data is transferred transaction completes.

 

But if you look frm broader percpective, it is a complete program or file being transferred but that is occuring thru different transactions tht start and finish delivering fragments of data between 2 entities.

 

 

Vinay

 

It is a basic unit of work, but we can nest them to create a more complex unit of work. I refering to transactions in database theory. You're in the right track but not complete

 

Link to comment
Share on other sites

Wood, I guess the links would be useful, some advice on choosing the links thou, do not trust the .com-s they're out there to make money, and usually are of no educational value. The best links would be from ieee.org, or acm.org, computer.org and of course .edu-s will be great, .net-s will be ok

 

here are a couple of links you might want to check, also any database book should help

 

http://user.it.uu.se/~arnoldp/distrib/TwoPhase.html

 

atomic transactions

http://faculty.cs.tamu.edu/bettati/Courses/662/Spring-2001/Slides/Handouts/hATrans.pdf

 

Link to comment
Share on other sites

they r atomic and do just for wht they r created for. If we nest them thn the whole purpose of atomicity will fail and it will be difficult to control thr operations.

 

wht if we create a nested transaction tht is desingned to update two related records. now wht happens if after exceution of one the system crashes, all the work will have to be redone and it will be more burdonesome to maintain reliability and integrity of system.

 

To avoid these complications we dont nest thm. infact this kinda scenario in distributed database will be disastrous when traffic between nodes will increase rapidly due to rollbacks and restarts.

Link to comment
Share on other sites

AlbaLed, how do your links refer to nested transactions, the subject of this thread? Am I missing something? I saw though you posted these links in the appropriate thread (two-phase commit).

 

I just posted these links to clarify that it is indeed possible to have nested transactions since Microsoft even implements it. The second link was to demonstrate that there's ongoing research in the area as well.

 

Wood

 

 

Link to comment
Share on other sites

Originally posted by wood

 

AlbaLed, how do your links refer to nested transactions, the subject of this thread? Am I missing something? I saw though you posted these links in the appropriate thread (two-phase commit).

 

I just posted these links to clarify that it is indeed possible to have nested transactions since Microsoft even implements it. The second link was to demonstrate that there's ongoing research in the area as well.

 

Wood

 

 

 

Vinay, you've got some good points in your reply, atomicity, integrity

 

So nested transactions introduce problems, like if-then-else -s if nested introduce ambiguity, but still in some way we can get rid of this ambiguity and nest if-then-else, why can't we do the same thing with transactions ????

 

wood

The two-phase commit thread is somewhat incorretly named if the problem applies to database systems that are not distributed.

 

Aha Microsoft implements them but is it safe to do so ?????

 

Check this out !!

 

A transaction is made up of:

Transaction begin (commit point, this is where we rollback if failure),

some actions which can fail

COMMIT

ROLLBACK

 

now we have a nested transaction

 

BEGIN T1

 BEGIN T2
    do some updates (T2)
 COMMIT or ROLLBACK
 
 do some updates (T1)
COMMIT or ROLLBACK

 

now say T2 COMMITs (a commit cannot be rolled back), and T1 fails, so it will need to do a ROLLBACK, but since changes made T2 cannot be undone the database is left at an inconsistent state.

 

If we modify transactions we can sure implement it, but by definition a transaction should obay the ACID-rule. One way to modify the transaction is if transactions are nested, commit only when the outermost transaction commits (match an else with the inner most unmatched else, any resemblance ????) From microsoft

 

"Calling ITransaction::Commit or ITransaction::Abort on the session object commits or aborts the transaction at the current (lowest) nesting level. "

 

So an unmodified transaction, which obeys the ACID-rule is NOT nestable

 

Does this help???

Link to comment
Share on other sites

...

 

So nested transactions introduce problems, like if-then-else -s if nested introduce ambiguity, but still in some way we can get rid of this ambiguity and nest if-then-else, why can't we do the same thing with transactions ????

 

We can. It indeed works fine.

 

The two-phase commit thread is somewhat incorretly named if the problem applies to database systems that are not distributed.

 

Ok.

 

Aha Microsoft implements them but is it safe to do so ?????

 

Yes.

 

Check this out !!

 

A transaction is made up of:

Transaction begin (commit point, this is where we rollback if failure),

some actions which can fail

COMMIT

ROLLBACK

 

now we have a nested transaction

 

BEGIN T1

 BEGIN T2
    do some updates (T2)
 COMMIT or ROLLBACK
 
 do some updates (T1)
COMMIT or ROLLBACK

 

now say T2 COMMITs (a commit cannot be rolled back), and T1 fails, so it will need to do a ROLLBACK, but since changes made T2 cannot be

undone the database is left at an inconsistent state.

 

Wrong. T2 changes were committed in the scope of T1. They will not be seen by anybody else until T1 gets committed.

 

"Changes made within the nested transaction are invisible to the top-level transaction until the nested transaction is committed. Even then, the changes are not visible outside the top-level transaction until that transaction is committed.".

 

If we modify transactions we can sure implement it, but by definition a transaction should obay the ACID-rule. One way to modify the transaction is if transactions are nested, commit only when the outermost transaction commits (match an else with the inner most unmatched else, any resemblance ????) From microsoft

 

"Calling ITransaction::Commit or ITransaction::Abort on the session object commits or aborts the transaction at the current (lowest) nesting level. "

 

Just read a little further. It says: "Committing or aborting a transaction commits or aborts all transactions nested below it as well.". So that means that T2 was not really committed in the database if T1 rolled back!

 

Wood

Link to comment
Share on other sites

Originally posted by wood

 

...

 

So nested transactions introduce problems, like if-then-else -s if nested introduce ambiguity, but still in some way we can get rid of this ambiguity and nest if-then-else, why can't we do the same thing with transactions ????

 

We can. It indeed works fine.

 

The two-phase commit thread is somewhat incorretly named if the problem applies to database systems that are not distributed.

 

Ok.

 

Aha Microsoft implements them but is it safe to do so ?????

 

Yes.

 

Check this out !!

 

A transaction is made up of:

Transaction begin (commit point, this is where we rollback if failure),

some actions which can fail

COMMIT

ROLLBACK

 

now we have a nested transaction

 

BEGIN T1

 BEGIN T2
    do some updates (T2)
 COMMIT or ROLLBACK
 
 do some updates (T1)
COMMIT or ROLLBACK

 

now say T2 COMMITs (a commit cannot be rolled back), and T1 fails, so it will need to do a ROLLBACK, but since changes made T2 cannot be

undone the database is left at an inconsistent state.

 

Wrong. T2 changes were committed in the scope of T1. They will not be seen by anybody else until T1 gets committed.

 

"Changes made within the nested transaction are invisible to the top-level transaction until the nested transaction is committed. Even then, the changes are not visible outside the top-level transaction until that transaction is committed.".

 

If we modify transactions we can sure implement it, but by definition a transaction should obay the ACID-rule. One way to modify the transaction is if transactions are nested, commit only when the outermost transaction commits (match an else with the inner most unmatched else, any resemblance ????) From microsoft

 

"Calling ITransaction::Commit or ITransaction::Abort on the session object commits or aborts the transaction at the current (lowest) nesting level. "

 

Just read a little further. It says: "Committing or aborting a transaction commits or aborts all transactions nested below it as well.". So that means that T2 was not really committed in the database if T1 rolled back!

 

 

not really commited - that goes against the definition of a transaction, once commited it can't be rolled back

 

Transactions are

1. Atomic (all or none)

2. Consistent - transform DB from one consistent state to another consistent state

3. Isolation - isolated from one another (that's what you're MS is stating)

4. Durability - once a transactions commits its updates survive in the db, even if the systems crashes right after commit (write-ahead log rule)

 

Where does "not really commited" fit in the transactions properties

Wood

Link to comment
Share on other sites

Article on nested transactions from SQL Server Books Online..

 

Wood

 

PS: I'm pretty sure Oracle has similar support but I have no manual for it... I'll ask the Oracle guru at work tomorrow.

 

 

 

 

 

Nesting Transactions

 

Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

 

The following example shows the intended use of nested transactions. The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it. If TransProc is called when a transaction is active, the nested transaction in TransProc is largely ignored, and its INSERT statements are committed or rolled back based on the final action taken for the outer transaction. If TransProc is executed by a process that does not have an outstanding transaction, the COMMIT TRANSACTION at the end of the procedure effectively commits the INSERT statements.

 

SET QUOTED_IDENTIFIER OFF

GO

SET NOCOUNT OFF

GO

USE pubs

GO

CREATE TABLE TestTrans(Cola INT PRIMARY KEY,

Colb CHAR(3) NOT NULL)

GO

CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS

BEGIN TRANSACTION InProc

INSERT INTO TestTrans VALUES (@PriKey, @CharCol)

INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)

COMMIT TRANSACTION InProc

GO

/* Start a transaction and execute TransProc */

BEGIN TRANSACTION OutOfProc

GO

EXEC TransProc 1, 'aaa'

GO

/* Roll back the outer transaction, this will

roll back TransProc's nested transaction */

ROLLBACK TRANSACTION OutOfProc

GO

EXECUTE TransProc 3,'bbb'

GO

/* The following SELECT statement shows only rows 3 and 4 are

still in the table. This indicates that the commit

of the inner transaction from the first EXECUTE statement of

TransProc was overridden by the subsequent rollback. */

SELECT * FROM TestTrans

GO

 

Committing inner transactions is ignored by Microsoft® SQL Server™. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

 

Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

 

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all the nested transactions, including the outermost transaction.

 

The @@TRANCOUNT function records the current transaction nesting level. Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all the nested transactions and decrements @@TRANCOUNT to 0. When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0 you are not in a transaction.

 

Link to comment
Share on other sites

Wood, you're missing the point

 

Transaction can be nested, but they need to be modified i.e. break the ACID-properties that transactions have.

 

By DEFINITION a transaction once commited cannot be rolled back. Do we agree at this point ? (if not then my friend, who's a teller at a bank can rollback the money I withdraw yesterday from an ATM)

if you agree with me at this point then you'll see why transactions (NOT modified transactions) cannot be nested.

 

 

Link to comment
Share on other sites

I don't think I'm missing the point...

 

Your operation at the bank is the outer transaction in this case. God knows (maybe some developers do) what thousands other inner transactions you must have triggered in their system to get your 20 bucks out of the machine!

 

We are getting at semantics now... transactions, modified transactions, my mama's transactions...! At the end, unless somebody finds it really worth to call it something else, they will still be refering to transactions, right? If somebody extends the basic concept of a transaction to encompass other functionalities, it might very well become a standard in the years to come (if they find it useful) and the basic definition (which I don't have) might even be revised... who knows?

 

Nested transactions is an interesting subject in many DB designs. Check out this guy's papers for example. Just do a simple search at acm.org and you'll find several articles.

 

I'm not sure where do you want to get with this. Are you saying that they don't exist? Or are you saying that even if they do, they shouldn't be calling transactions since they violate properties of the "transaction definition"?

 

Wood

 

Link to comment
Share on other sites

don't wana get nowhere wood, simply following definitions, that's what's important for this test. I have seen a couple of questions where definition matter, remember Shell sort? I told you commercial sites having nothing to do with their educational counter parts, this not only my opinion. So whatever you wana think its fine, I don't wana push this any further

 

 

Link to comment
Share on other sites

on final note as albaled has already written, a transaction is not a transaction until it has ACID properties and if we nest them thn they r broken, so basically thrs nothing as nested transaction.

 

Secondly, wood in case of transactions scope is not applicable. transaction performs sum read and write operations from and on database and once committed means committed it doesnt matter whether you have nested it or not.

 

albaled, nesting of if-then-else and transaction r different things. when we talk abt if-thn-else we generally talk abt programs tht r run in the memory but transactions deals with data tht may be large and difficult to undo if sum error occurs.

 

although we can use nested if-thn-else to perform sum operation tht is permanent in nature but tht generally is regarded as bad practice coz reliability is put at risk.

 

We r talking here abt theoretical concept of transaction and practically anythin can be changed by any vendor to make its product different and thts wht the case with microsoft.

Link to comment
Share on other sites

For curiosity:

 

Transaction, as we know, is being refered by researchers in the field as "flat transactions" to differentiate between other models such as nested transactions, which relax some of its ACID properties.

 

On ongoing research in the area, there's the concept of flat transaction (the transaction conforming to all ACID rules), open/closed nested transactions, sagas, and others. Nested transactions obviously relax some ACID rules (I believe the D rule) and are a generalization of a savepoint (to support intra-transaction parallelism) or a relaxation of a flat transaction, depending on who's leading the research nomenclature.

 

Some of them classify nested transactions as Advanced Transaction Models, together with others:

http://www.cse.sc.edu/~eastman/CSCI721/89

 

Nice presentation depicting all sort of transactions:

http://www.dsg.technion.ac.il/misc/softtalk/Transactions95.ppt

 

Nice paper depicting flat, open/closed nested transactions:

http://www.dvo.ru/bbc/pdpta/vol1/ps1142.pdf

 

I believe it is not a common accepted concept/standard yet as most of you pointed out. However, since I've seen it working before and there are real advantages for doing it (see second PP presentation), I believe it was worth mentioning that it indeed exists for those you didn't know.

 

Let's hope there's some question about transactions, ACID rules, etc... on the test! We'll be glad we talked about it!

 

Wood

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...