7

I have recently come across a concept of transactions. Seems like they are a crucial feature for production applications, yet I have never heard about them except once in a university. I can't remember a single tutorial on youtube / udemy that actually manipulated with db using transactions.

Maybe if so few people talk about it, it (now) happens (somehow) automatically? Do I have to implement it explicitly to make my db consistent? I use postgresql.

4
  • 2
    Transactions are necessary whenever there is concurrent access to the database - so in multi-user or multi-thread contexts. In SQL, each 'statement' is supposed to be transactionally consistent by default. Where there are multiple statements, it is possible to explicitly declare that these should occur as a single transaction. If you know nothing about transactions, then the best place to start would be to read some information online about transactions, such as: docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
    – Steve
    Mar 6 at 15:42
  • 2
    As @Steve said, transactions are used in systems designed for reliability in all cases. As for why you don't see them in use more often? I refer you to Sturgeon's Law. Mar 6 at 15:45
  • Do you use a particular ORM or application framework? Many modern frameworks will automatically use transactions (often with default behavior around rolling things back if there are errors), so it's worth knowing how the frameworks you use work and how to use them to control transactions where necessary. If you mention what you use, someone can help identify resources on that. Mar 7 at 0:33
  • 1
    Also, transactions are, how shall I put it, extremely highly recommended if you're ever messing around with a production database :) - see: this and this Mar 7 at 19:58
10

Transactions are important for situations where you have multiple clients attempting to read/write the database at the same time. Andrew's answer also makes the point about grouping statements together. Depending on how you are accessing the database, transactions might be happening at an API-level where you are not aware of it. I think some ORM frameworks will automatically wrap your statements in transactions. Some databases can also operate in a "auto-commit" mode where every statement gets wrapped in a transaction at the database level.

You might not have seen a tutorial on them as some people might consider them an "advanced" topic, beyond basic DML/DDL statements, but I can assure you, they are very important, and it's good that you're taking the time to learn about them, even if you don't need to code them explicitly in your current project.

For interest's sake: I searched "postgres transaction tutorial" and discovered this: https://www.tutorialspoint.com/postgresql/postgresql_transactions.htm - but it's not a YouTube video.

2
  • 2
    One thing you should mention is that a transaction "that you are not aware of" doesn't help. Only the application level can know what a consistent vs. inconsistent state is, and thus which statements have to be grouped into one transaction. This knowledge isn't available at framework/API level. Mar 10 at 11:50
  • @RalfKleberhoff That's a good point. Mar 10 at 17:57
8

Transactions are also important when a group of changes must be treated as a coherent set: either all of the changes must be applied, or none of them.

If you don't use transactions in cases like that, if any update or insert after the first fails for some reason, the previous updates or inserts would remain.

If all updates or inserts in a set are done in a transaction, if any updates or inserts fail, the entire transaction can be rolled back and the database will be as if none of the updates or inserts ever happened.

2

Even software running just on your home laptop should/will use transactions. For example, your software decides that one value in the database is increased, and another is decreased by the some amount, and then the whole operation is removed from a task list in the database.

You make that a transaction so if the software crashes you will have either no change or all three changes in the database. Not just the first change or the first two changes. So if you restart the app after the crash, either the whole operation has been performed and is gone from the task list, or the operation is still in the task list and you can repeat it.

Without transactions you might find yourself in a situation where the increase has happened but not the decrease, or both have happened but the operation is still in the task list and you do it twice. Lots of opportunities to mess up your database.

1

Full ACID transactions are extremely important to ensure that your data is always consistent. All mature Relational Database Management Systems (RDBMS) support them, and PostgreSQL is not an exception (good choice).

The drawback of ACID is that it can slow down your application to provide those guarantees, so some RDBMS don't support them or they are disabled by default. If by DB you are also referring to the NoSQL products like MongoDB, be aware that they are using concepts like "eventual consistency" which basically means no full ACID guarantees. Depending on your application and how critical your data is, it is up to you to decide what suits you best.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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