Transactions for single table operation
-
Hi all, Today one of my friend had his sql queries and corresponding C# code reviewed by his boss. It so happened that one particular stuff, which create, read, update and deletes a master table, was not under transaction control. (I believe you do not need a transaction if it operates on only one table). His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. What is your say in this regard? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
-
Hi all, Today one of my friend had his sql queries and corresponding C# code reviewed by his boss. It so happened that one particular stuff, which create, read, update and deletes a master table, was not under transaction control. (I believe you do not need a transaction if it operates on only one table). His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. What is your say in this regard? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
Microsoft SQL Server Performance Tuning and Optimization - lots of good tips. In any case, I think you're wrong in that "only operates on one table". You might be confusing the row-level-locking mechanism, which acts as a mutex for the row data. Transactions are the equivilant of try-catch-throw, and provide a nice rollback functionality. However, if you're really worried about performance, batch up the operatations and carry them out inside of a single transaction (this also has other issues of course). You might also want to benchmark with and without on a copy of the database and see what, if any, impact it has. I sounds to me like the "Crazy boss" has had issues in the past where database updating errors were not being caught and the database tables got corrupted.
Todd C. Wilson (meme@nopcode.com) NOPcode.com Skinning Toolkit MP3 Server for Windows And Lots More "The source, it was leaked : therefore, it must be rewritten."
-
Hi all, Today one of my friend had his sql queries and corresponding C# code reviewed by his boss. It so happened that one particular stuff, which create, read, update and deletes a master table, was not under transaction control. (I believe you do not need a transaction if it operates on only one table). His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. What is your say in this regard? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
-
Not enough information. Do multiple operations need to operate atomically? Tim Smith I'm going to patent thought. I have yet to see any prior art.
Tim Smith wrote: Not enough information. Do multiple operations need to operate atomically? I forgot to explain the sample. Just imagine you need to insert/update/delete a country master table. i am just inserting country id and country name. Do you think we need to use transaction for this scenario too? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
-
Microsoft SQL Server Performance Tuning and Optimization - lots of good tips. In any case, I think you're wrong in that "only operates on one table". You might be confusing the row-level-locking mechanism, which acts as a mutex for the row data. Transactions are the equivilant of try-catch-throw, and provide a nice rollback functionality. However, if you're really worried about performance, batch up the operatations and carry them out inside of a single transaction (this also has other issues of course). You might also want to benchmark with and without on a copy of the database and see what, if any, impact it has. I sounds to me like the "Crazy boss" has had issues in the past where database updating errors were not being caught and the database tables got corrupted.
Todd C. Wilson (meme@nopcode.com) NOPcode.com Skinning Toolkit MP3 Server for Windows And Lots More "The source, it was leaked : therefore, it must be rewritten."
Before i accept or deny i would like to learn some more information from you. Let me give you the scenario. I just want to insert a record to master table. for this scenario there are only two possible results. The record will be inserted or some error could happened. Since i have a robust exception handling mechanism i can trap exceptions if any and proceed accordingly. If no exception i guess i am assured of my data being inserted. Consider using transaction for this scenario. What will you do when some exception occurs: In the catch block you will say rollback. What is there to rollback?? becaz we performed no prior successful transaction to be rolled back. So i feel for this scenario rollback gives no benefit. Please correct me if i am wrong Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
-
Hi all, Today one of my friend had his sql queries and corresponding C# code reviewed by his boss. It so happened that one particular stuff, which create, read, update and deletes a master table, was not under transaction control. (I believe you do not need a transaction if it operates on only one table). His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. What is your say in this regard? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
Venkatraman wrote: I believe you do not need a transaction if it operates on only one table i dont think this statement is right. transaction has got nothing to with the tables u act upon in a transaction. if u want to move from consistent state to another consistent state u need transactional properties to ensure that. if ur friend does insert/update/delete, if he does all in one transaction and then aborts it and then all of them will get rolledback. if he does each one in one transaction then rollback will be only till last operation. so i assume his boss wants him do either all insert/update/delete or nothing, in which case u need a transaction. one more thing, even if application doesnt start transaction, all databases by default will internally start a transaction and then perform the operation(as like autocommit mode). so u arent going to get any performance difference.
-
Venkatraman wrote: I believe you do not need a transaction if it operates on only one table i dont think this statement is right. transaction has got nothing to with the tables u act upon in a transaction. if u want to move from consistent state to another consistent state u need transactional properties to ensure that. if ur friend does insert/update/delete, if he does all in one transaction and then aborts it and then all of them will get rolledback. if he does each one in one transaction then rollback will be only till last operation. so i assume his boss wants him do either all insert/update/delete or nothing, in which case u need a transaction. one more thing, even if application doesnt start transaction, all databases by default will internally start a transaction and then perform the operation(as like autocommit mode). so u arent going to get any performance difference.
Ganesh Ramaswamy wrote: Venkatraman wrote: I believe you do not need a transaction if it operates on only one table i dont think this statement is right. transaction has got nothing to with the tables u act upon in a transaction Let me first ask apologies for not putting it correctly.. I know what is transaction really mean but what i mean to say is "with in a db session if you are performing just one record insertion into one master table, you do nothing other than this". My question is, you would use transaction if you want to maintain the consistency or the same state when ever there is a failure during more than one operation performed with in a transaction context". In my scenario it is single operation. Thatz why i asked will you use transaction contexts if it involves modifying state of one table. I hope i put it across this time correctly.. now give your input thanks Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
-
Hi all, Today one of my friend had his sql queries and corresponding C# code reviewed by his boss. It so happened that one particular stuff, which create, read, update and deletes a master table, was not under transaction control. (I believe you do not need a transaction if it operates on only one table). His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. What is your say in this regard? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
Venkatraman wrote: His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. On a single step there should be no problems skipping transactions "normally". After all, transactions are there to make sure that in multiple steps if something goes wrong somewhere down the update, it can roll back to the state it was at originally. However, you might run into a problems if there happens to be any triggers on the table which gets other processes involved. It is safer to use them and I doubt there would be any performance penalty, but in the real world a large portion of database code out there does not specifically use them for single step queries and many do not even with multiple step. A lot just depends on if you have total control on that database and know there will not be outside actions such as triggers occuring on your data. Most of the time this is not the case and you are safe. Rocky <>< www.GotTheAnswerToSpam.com
-
Hi all, Today one of my friend had his sql queries and corresponding C# code reviewed by his boss. It so happened that one particular stuff, which create, read, update and deletes a master table, was not under transaction control. (I believe you do not need a transaction if it operates on only one table). His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. What is your say in this regard? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
Transactions also have Concurrency property. It guards against dirty reads, Phantom rows, etc depending upon isolation level of Transaction. If there is only one user only, there is no need if rollback is handled manually. but for multiple users it is definitely required. Deepak Khajuria
-
Hi all, Today one of my friend had his sql queries and corresponding C# code reviewed by his boss. It so happened that one particular stuff, which create, read, update and deletes a master table, was not under transaction control. (I believe you do not need a transaction if it operates on only one table). His boss is so crazy and asking him to use transaction when ever insert/update/delete operations are performed. What is your say in this regard? Cheers, Venkatraman Kalyanam Bangalore - India "Being Excellent is not a skill, it is an attitude" Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
A single statement in SQL is atomic - either the entire operation performed by the statement is committed to the database, or none of it is. For example, if you execute an INSERT/SELECT (inserting multiple rows into the database), four rows are produced by the SELECT, and three rows could be inserted, but one causes a primary key violation, none of the four rows are inserted. If you need more than one statement to be performed atomically, you need a transaction.