4

I just realized I've had a headache for years. Well, metaphorically speaking. In reality I was looking at my database structure and somehow just realized I never use transactions. Doh.

There's a lot of data on the internet about transactions (begin transaction, rollback, commit, etc.), but surprisingly not much detail about exactly why they are vital, and just exactly how vital?

I understand the concept of handling if something goes wrong. This made sense when one is doing multiple updates, for example, in multiple tables in one go, but this is bad practice as far as I know and I don't do this. All of my queries just update one table. If a query errors, it cancels, transaction or no transaction. What else could go wrong or potentially corrupt a one table update, besides my pulling the plug out of my server?

In other words, my question is,

exactly how vital is it that i implement transactions on all of my tables - I am fully blasphemous for not having them, or does it really matter that much?

UPDATE

+1 to invisal, who pointed out that queries are automatically wrapped as transactions, which I did not know. Pointed out multiple good references on the subject of my question.

Community
  • 1
  • 1
dthree
  • 19,847
  • 14
  • 77
  • 106

5 Answers5

4

This made a lot of sense when one is doing multiple updates, for example, in multiple tables in one go. But basically all of my queries just update one table at a time. If a query errors, it cancels, transaction or no transaction.

In your case, it does nothing. A single statement has its own transaction itself. For more information you can read the existed question and answers:

Community
  • 1
  • 1
invisal
  • 11,075
  • 4
  • 33
  • 54
  • Ah - I didn't know that. Thanks for the links - I am studying these. So I should technically be totally fine? – dthree Oct 06 '13 at 05:10
  • @dc2, I believe those links contains a pretty good explanation. To me, I never use transaction for a single statement. For single statement, I use it in for unit test only. And I always use it to group many statements into single transaction to ensure data integrity. – invisal Oct 06 '13 at 05:14
1

Most important property of the database is to keep your data, reliably.

Database reliability is assured by conforming to ACID principles (Atomicity, Consistency, Isolation, Durability). In the context of databases, a single logical operation on the data is called a transaction. Without transactions, such reliability would not be possible.

In addition to reliability, using transactions properly lets you improve performance of some data operations considerably. For example, you can start transaction, insert a lot of data (say 100k rows), and only then commit. Server does not have to actually write to disk until commit is called, effectively batching data in memory. This allows to improve performance a lot.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • How would this improve performance? The server still has to do all of the work - it only delays in handle the disk writing part. I don't see how this improves anything. – dthree Oct 06 '13 at 05:14
  • Without wrapping into big transaction, server has to write every single insert into disk and wait until it is flushed to ensure ACID. Doing that 100k times is very slow – mvp Oct 06 '13 at 05:16
1

You should be aware that every updating action against your database is performed inside a transaction, even if only 1 table (SQL server automatically creates a transaction for it). The reason for always doing transactions is to ensure ACID as others have mentioned. Here I'd like to elaborate on the isolation point. Without transaction isolation, you may have problems with: read uncommitted, unrepeatable read, phantom read,..

Khanh TO
  • 48,509
  • 13
  • 99
  • 115
0

it depends if you are updating one table and one row, then the only advantage is going to be in the logging... but if you update multiple row in a table at one time... without transactions you could still run into somecurruption

Greggpb
  • 11
  • 2
  • Thanks, but i'm looking for a bit more of a detailed/deeper answer that I haven't been able to find elsewhere, i.e. what would cause that corruption, etc. – dthree Oct 06 '13 at 05:07
0

Well it depends, SQL is most of the times used for supporting data for some host languages like c, c++, java, php, c# and others. Well I have not worked with much technologies.. but if you are using following combinations then here is my point of view:

SQL with C / C++ : Commit Required

SQL with Java : Not Required

SQL with C# : Not Required

SQL with PHP : Not Required

And it also depends which SQL you are using. It would also depend from different flavors of SQL like Oracle SQL, SQL Server, SQLite, MySQL etc...

When you are using Oracle SQL in its console, like Oracle 11g, Oracle 10g etc... COMMIT is required.

And as far as corruption of table and data is concerned. YES it happens, I had a very bad experience with it. So, if you pull out your wire or something while you are updating in your table, then you might end up with a massive disaster.

Well concluding, I will suggest you to do commit.

Veer Shrivastav
  • 5,434
  • 11
  • 53
  • 83
  • 2
    if commit is not explicitly required, it does not mean it is not happening - it most certainly is – mvp Oct 06 '13 at 05:08
  • 1
    Yes, true. I also meant that. – Veer Shrivastav Oct 06 '13 at 05:10
  • Why would languages make a difference? How they handle the database API? – dthree Oct 06 '13 at 05:11
  • 1
    As far as API is concerned, you don't have same API for all the languages. And languages like c/c++ , python, cobol have different APIs. No such standard API like Java. And especially, when c/c++ is concerned, you don't have API rather you have Connectivity Interface like OCCI and Pro*C, there these API gives you have to commit explicitly. – Veer Shrivastav Oct 06 '13 at 05:15