Atomic Sql Statements (Transaction)
-
I read an article here[^] describing how to perform SQL operations in an atomic manner using transactions. I just do not understand what the usage of a transaction is in the following example (I didn't ask my question below that article because the author has not answered asked questions).
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASBEGIN TRAN
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLERCOMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1My question is, if the the first insert statement failed, why do we need to roll back it? No insertion to database happened, therefore we do not need to rollback anything! If I'm right, what's the problem with the following sproc:
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASINSERT titles(title\_id, title, type) VALUES (@title\_id, @title, @title\_type) IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLER RETURN 0ERR_HANDLER:
RETURN 1 -
I read an article here[^] describing how to perform SQL operations in an atomic manner using transactions. I just do not understand what the usage of a transaction is in the following example (I didn't ask my question below that article because the author has not answered asked questions).
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASBEGIN TRAN
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLERCOMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1My question is, if the the first insert statement failed, why do we need to roll back it? No insertion to database happened, therefore we do not need to rollback anything! If I'm right, what's the problem with the following sproc:
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASINSERT titles(title\_id, title, type) VALUES (@title\_id, @title, @title\_type) IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLER RETURN 0ERR_HANDLER:
RETURN 1 -
I read an article here[^] describing how to perform SQL operations in an atomic manner using transactions. I just do not understand what the usage of a transaction is in the following example (I didn't ask my question below that article because the author has not answered asked questions).
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASBEGIN TRAN
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLERCOMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1My question is, if the the first insert statement failed, why do we need to roll back it? No insertion to database happened, therefore we do not need to rollback anything! If I'm right, what's the problem with the following sproc:
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASINSERT titles(title\_id, title, type) VALUES (@title\_id, @title, @title\_type) IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLER RETURN 0ERR_HANDLER:
RETURN 1I guess I found my answer, it's useful when the first statement succeeds and the second one doesn't. At that time we need to rollback the first successful statement.
-
In your version if the second insert fails, then an orphaned title has been added without its associated author.
You are right, I just figured it out :) Thank you
_
-
I read an article here[^] describing how to perform SQL operations in an atomic manner using transactions. I just do not understand what the usage of a transaction is in the following example (I didn't ask my question below that article because the author has not answered asked questions).
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASBEGIN TRAN
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLERCOMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1My question is, if the the first insert statement failed, why do we need to roll back it? No insertion to database happened, therefore we do not need to rollback anything! If I'm right, what's the problem with the following sproc:
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
ASINSERT titles(title\_id, title, type) VALUES (@title\_id, @title, @title\_type) IF (@@ERROR <> 0) GOTO ERR\_HANDLER INSERT titleauthor(au\_id, title\_id) VALUES (@au\_id, @title\_id) IF (@@ERROR <> 0) GOTO ERR\_HANDLER RETURN 0ERR_HANDLER:
RETURN 1There are several considerations in both examples. Some of them are: 1. Transaction usage and scope: In the first example transaction is started and ended inside stored proc. Personally I don't feel this is a good way to handle transactions. Consider what would happen if the real world situation involves call to two stored procs. If both of them autonomously end transaction, there's no quarantee that the result is correct. This is why I always start and end transactions at calling side. 2. Why rollback if first statement fails: Even though the insert itself fails, transaction is started and DML is executed. This has resulted to resource usage. The only way to free those resources is to roll back the transaction (since there's no point to commit a failure). So basically I would prefer the second version of the stored proc, but I also would ensure that it's executed inside a transaction. This would make it possible to have larger scope transactions and also to retry failed operations inside a transaction which is a very elemental requirement in certain system types. Mika
The need to optimize rises from a bad design. My articles[^]
-
There are several considerations in both examples. Some of them are: 1. Transaction usage and scope: In the first example transaction is started and ended inside stored proc. Personally I don't feel this is a good way to handle transactions. Consider what would happen if the real world situation involves call to two stored procs. If both of them autonomously end transaction, there's no quarantee that the result is correct. This is why I always start and end transactions at calling side. 2. Why rollback if first statement fails: Even though the insert itself fails, transaction is started and DML is executed. This has resulted to resource usage. The only way to free those resources is to roll back the transaction (since there's no point to commit a failure). So basically I would prefer the second version of the stored proc, but I also would ensure that it's executed inside a transaction. This would make it possible to have larger scope transactions and also to retry failed operations inside a transaction which is a very elemental requirement in certain system types. Mika
The need to optimize rises from a bad design. My articles[^]
Re: your point 1 The only time I've written stored procedures was when I was using ad hoc ODBC statements (about ten years ago) and had no other way to do transactions. Currently, with ADO.net, I can do transactions in my program so I don't use stored procedures at all. However, a properly designed system of stored procedures should provide everything the application needs, including transactioning. The application shouldn't need to know about the details of the schema and what the stored procedures are doing, and therefore should be able to just execute the stored procedures without thought to whether or not to use transactions.
-
Re: your point 1 The only time I've written stored procedures was when I was using ad hoc ODBC statements (about ten years ago) and had no other way to do transactions. Currently, with ADO.net, I can do transactions in my program so I don't use stored procedures at all. However, a properly designed system of stored procedures should provide everything the application needs, including transactioning. The application shouldn't need to know about the details of the schema and what the stored procedures are doing, and therefore should be able to just execute the stored procedures without thought to whether or not to use transactions.
I think there are several factors that affect the choice how transactions should be used. In my example I used two separate procedures that are executed in a sequence. If the execution as a whole should have ACID properties, the must be wrapped inside a common transaction. Basically this gives two options: 1. create a new stored procedure combining both calls thus leading to single call to backend 2. use transaction with a scope over two different SQL calls In point 1, transaction problem is solved (in a simple case) but also the backend now knows (at least partly) the business logic (order of operations and the sequence). This may or may not be acceptable. The problem becomes more difficult if the procedures are not located in the same backend In point 2, we would need a higher level transaction manager (such as classes in System.Transactions namespace or even self made mechanism). This adds extra 'overhead' to the programming but also it's possible to have more resources inside the same transactions. If this is needed, is again depending on the requirements of the system. One good link that came in mind: http://blogs.msdn.com/diegumzone/archive/2006/08/14/699219.aspx[^]
The need to optimize rises from a bad design. My articles[^]
-
I think there are several factors that affect the choice how transactions should be used. In my example I used two separate procedures that are executed in a sequence. If the execution as a whole should have ACID properties, the must be wrapped inside a common transaction. Basically this gives two options: 1. create a new stored procedure combining both calls thus leading to single call to backend 2. use transaction with a scope over two different SQL calls In point 1, transaction problem is solved (in a simple case) but also the backend now knows (at least partly) the business logic (order of operations and the sequence). This may or may not be acceptable. The problem becomes more difficult if the procedures are not located in the same backend In point 2, we would need a higher level transaction manager (such as classes in System.Transactions namespace or even self made mechanism). This adds extra 'overhead' to the programming but also it's possible to have more resources inside the same transactions. If this is needed, is again depending on the requirements of the system. One good link that came in mind: http://blogs.msdn.com/diegumzone/archive/2006/08/14/699219.aspx[^]
The need to optimize rises from a bad design. My articles[^]
I suppose two phase commit would be another reason to avoid stored procedures and just write the stuff in code. :-D
-
I suppose two phase commit would be another reason to avoid stored procedures and just write the stuff in code. :-D