1

Let's assume that two transactions execute on a database, both reading, and writing. At some time, the system crashes. After the server is restarted, the database and the backups were destroyed by the crash and the database does no longer exists. Which ACID properties would this break?

My first guess is that it breaks only Durability since we can assume that transactions did nothing to the database ensuring atomicity, the database is definitely consistent, and I am not sure about isolation, but I don't see how would this break isolation.

2
  • 3
    In reality, the DBA would be searching for a new job. 1st rule of a DBA is "Tho shall not lose data". Nov 24, 2021 at 20:15
  • 4
    If you can say that a non-existent database is in a consistent state, you can also say that it is in a durable state.
    – mustaccio
    Nov 24, 2021 at 22:42

2 Answers 2

2

It wouldn't break any ACID properties regarding the single database, because neither the database nor the backups exist after the incident.

ACID are are a set of properties regarding transactions:

In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps. In the context of databases, a sequence of database operations that satisfies the ACID properties (which can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

Reference: ACID (Wikipedia)

You wouldn't know the state of the ACID properties until you restored the database, which you can't.

Atomicity

Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.

Answer: We don't know

Consistency

Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.

Answer: We don't know

Isolation

Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. Isolation is the main goal of concurrency control; depending on the method used, the effects of an incomplete transaction might not even be visible to other transactions

Answer: We don't know

Durability

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

Answer: We don't know

However

...you could start making well guessed hypothetical assumptions:

Durability probably went out the window, because the transaction never ended and probably didn't roll back.

Consistency probably failed, because the state became unknown.

Atomicity possibly failed, because it's not guaranteed that the data was correcty written to the database.

Isolation probably won, because transactions are mostly conducted in some sequential order as defined by the database settings and session settings. But that is highly speculative. It depends on the order of the total system failure and how it unfolded internally.

2

After the server is restarted, the database and the backups were destroyed by the crash and the database does no longer exists.

If a server failure has destroyed both the database and its backups, then whoever set up the backups is, frankly, incompetent.
The whole point of Backups - indeed, the only reason that we expend so much time, effort and money on them at all - is so that they can be Recovered even if you lose your entire machine estate and have to rebuild everything using new servers driven onto your site on the back of great, big lorries (Ye Olde Schoole of Recovery).

OK, everything's "Virtual" these days, but the same principle applies.
If you take your backups on a VM on the same Hypervisor as the database server, do you really have any protection if the hypervisor blows up?

Which ACID properties would this break?

ACID describes behaviours inside a properly working database, in much the same way that an operating manual might describe the proper working of, say, a carriage clock.
If you drop the clock off a tall building, it's not likely to tell the time reliably, or at all, afterwards.
Same goes for your "broken" database.

But at least, with proper Backups, you can reconstruct your database (no-one's figured out an equivalent for the clock, yet).

2
  • The 3-2-1 plan is a pretty good minimum standard for backups: vmwareblog.org/3-2-1-backup-rule-data-will-always-survive (minimum as there is no harm in taking extra precautions, especially for mission critical data!). Coincidentally came up in a HN post today, see that for further discussion: news.ycombinator.com/item?id=29333351 Nov 25, 2021 at 11:34
  • Clock? I see 3 different clocks using 2 different power sources in the room I'm in (battery, power cord) Then there is the one clock that's in my car. (1 copy off site). When a clock breaks, you always do a bare metal recovery of the data it contains (current time) Nov 25, 2021 at 12:17

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .