Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am looking at a codebase where a developer never uses SQL transactions i.e. each update/insert into the SQL database is an atomic operation.

I believe the codebase would benefit from transactions. I was looking online today and in one Stackoverflow question an answerer says: "In some situations it is better to have a partial update rather than none at all". Is there any truth in this? I suppose it depends on the problem domain to some extent.

share|improve this question
2  
What is it doing? A partial update in finance is likely much worse than none. A partial update to a log file is likely 'ho hum'. –  MichaelT Apr 25 '13 at 19:04
    
There are no Transactions. I was taught that if an operation inserts/updates two or more tables then it should be part of a Transaction. I am wandering if there are scenarios when this statement is false. –  w0051977 Apr 25 '13 at 19:06
    
@MichaelT,it is a high volume website. This article seems to hold true: martinfowler.com/bliki/Transactionless.html –  w0051977 Apr 25 '13 at 19:16
1  
I have worked on high volume finance web sites, and high volume bug tracking web sites. On the bug tracking, I had auto commit turned on - this was for ease of use more than performance. In finance, it was all transactions that reflected one consistent set of state changes. –  MichaelT Apr 25 '13 at 19:20
    
@MichaelT, thanks +1. Can you post an answer so that I can give credit? –  w0051977 Apr 25 '13 at 19:24

2 Answers 2

It depends not just on the problem domain, but also on technical considerations. Basically, it comes down to the simple question: "Will there be unacceptable problems when a partial update happens?" Some examples:

  • Transfering money from one account to another. A partial update is completely unacceptable for domain reasons.
  • Adding a vote to an online poll and incrementing a result count (which is only for display, with the final result being recalculated from the vote table). A partial update is probably acceptable.
  • Adding something to an n:m relationship. A partial update would result in an inconsistent state where you have either an entry that is not connected, or a connection without a matching entry. This is unacceptable for technical reasons - you'd get an error whenever that entry is encountered and it would likely be impossible to fix this error within the system.
  • A hardware unit that sends batches of sensor data to be stored. Adding only some of the entries of a batch is probably better than losing all of it.

Honestly, it's really rare for partial updates to be tolerable.

share|improve this answer
    
Thanks +1. What are your thoughts on this article? martinfowler.com/bliki/Transactionless.html –  w0051977 Apr 25 '13 at 19:46
1  
@w0051977: The key statement is "You have to pay attention to the order of your commits, getting the more important ones in first. At each commit you have to check that it succeeded and decide what to do if it fails." - that's quite a lot of additional work compared to using transactions, and very error-prone. You're manually doing something very similar to what a DB does when you use transactions. But it's hand-tailored for every single operation, which allows you to do only the minimal work necessary to maintain exactly as much integrity as you need. –  Michael Borgwardt Apr 25 '13 at 20:51

From the database perspective, frequent commits are often used to prevent records being unreadable by other sessions as they are locked by the updating session.

However this varies by RDBMS. Oracle has always had an MVCC system that prevented readers from blocking writers and writers from blocking readers, so it is not a problem there for instance.

Over-frequent committing on Oracle is strongly discouraged not only because it can leave partial transactions in the system but also because a COMMIT requires a synchronous redo log buffer flush which stalls the session (a high level of log_file_sync wait events is symptomatic of over-committing). Redo log buffers are asynchronously flushed every few seconds or when they are X% full anyway, so a longer running transaction may have less work to do during an explicit commit if it is deferred until the end of the business transaction anyway.

share|improve this answer
    
Thanks. I am not sure how this answers my question. Are you saying it is acceptable not to use transactions in high volume websites? What is a high volume website? I understand it to mean a website that is a front end to a database containing lots of records but I am unsure. –  w0051977 Apr 26 '13 at 8:56
    
What I'm saying is that one consideration in deciding whether to commit frequently is the database technology. On some, for example Oracle, frequent commits will be slower. On others frequent commits are required to avoid locking problems. what database is used for the system you are using? –  David Aldridge Apr 26 '13 at 9:03
    
Thanks. The system in question queries multiple systems (using SELECT), but only updates/deletes/inserts from/into one SQL Server database. –  w0051977 Apr 26 '13 at 9:14
    
The use of SQL Server may explain why frequent commits are issued. –  David Aldridge Apr 26 '13 at 9:17
    
Thanks. Could you clarify why it may explain why frequent commits are issued? +1. –  w0051977 Apr 26 '13 at 9:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.