CODEWITHSUNDEEP
sql
dthree
dthree

Reputation: 20730

SQL - Why transactions?

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.

Upvotes: 4

Views: 969

Answers (5)

Khanh TO
Khanh TO

Reputation: 48972

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,..

Upvotes: 1

Veer Shrivastav
Veer Shrivastav

Reputation: 5496

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.

Upvotes: 0

mvp
mvp

Reputation: 116048

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.

Upvotes: 1

invisal
invisal

Reputation: 11171

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:

Upvotes: 4

Greggpb
Greggpb

Reputation: 11

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

Upvotes: 0

Related Questions