CODEWITHSUNDEEP
mysqlsqlsql-serverpostgresqltransactions
Yoaz Menda
Yoaz Menda

Reputation: 1706

Is an ACID complaint SQL database is still "ACID" even when not explicitly using transactions?

let's say I'm using a standard SQL engine (e.g. Postgres, MySQL...).

If I'm not explicitly saying "Begin transaction" and "end transaction" when making a query

(like "INSERT INTO students ..."),

  1. will it be "safe"?

  2. do I lose the property of "ACID" that is implemented by the RDBMS? can my application now be inconsistent if many requests are made?

  3. does the RDBMS guaranties ACID only when I use transactions?

  4. does every write-query I make in my application code have to be a transaction to enjoy the ACID property in my application?

Upvotes: 2

Views: 511

Answers (1)

Ron Ballard
Ron Ballard

Reputation: 701

The behaviour you are asking about does vary from one database to another.

With PostgreSQL and Microsoft SQL Server, if you don't use "begin transaction" ... "end transaction" then every statement is treated as a transaction. This behaviour is often described as "auto commit" and you can switch that on as an option in MySQL.

A transaction should be defined by what your application needs. The classic case is if you buy something from me then the money must leave your account and arrive in mine as one transaction. Either both actions succeed or both fail. So in this case, even with auto commit, you must put "begin transaction" ... "end transaction" around the two statements, otherwise the money might leave your account and not arrive in mine, and we'll both be unhappy.

In fact the banking transaction is more complex than that; this is a simple illustration.

Many applications require multiple SQL statements to happen as a unit, and for these you always need transactions.

The variety of behaviour means you must check the documentation. Some of the major databases (not the three mentioned above) start a new transaction on various events such as the end of the previous transaction, or a DDL statement (create, drop, alter and truncate in some cases). If you need transactions, and most real-world applications do, then you must check the documentation for the database that you are using, and do lots of multi-user testing.

There is a longer explanation here and the database documentation for the database you choose to use is the authoritative source.

Upvotes: 3

Related Questions