CODEWITHSUNDEEP
mysqloracle-databasepostgresqlserializabletransaction-isolation
netok
netok

Reputation: 211

Does Serializable isolation level protect databases against ACIDRain attack?

ACIDRain attack paper by Todd Warszawski, Peter Bailis.

A high-level overview blogpost on this paper.

Many applications were found to be vulnerable to this, eg. WooCommerce, Opencart.

There are two types of anomaly that ACIDRain attacks could trigger, dependent on the application involved:

  1. Level-based isolation anomalies, which are races due to isolation settings at the database-level i.e. the database may not support serializability, or may not have been configured to do so (this is the case for most deployed databases in the wild).
  2. Scoping isolation anomalies, which occur when an application programmer fails to correctly encapsulate logic using transactions. This enables concurrent requests to impact behavior that could not have arisen sequentially.

It sounds like both can be solved by enforcing Serializable isolation level for transactions. Is it correct?

Also, some database doesn't have real Serializable isolation level, for example Oracle. What can be done to protect them from this type of attack?

Upvotes: 3

Views: 655

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247575

In PostgreSQL the case is simple: if you use the SERIALIZABLE isolation level, you are automatically safe from such attacks. This is because SERIALIZABLE in PostgreSQL guarantees “true” serializability: if all transactions involved run at that level, the result of the workload is equivalent to some serialized execution of the transactions. No anomalies are possible.

The price you are paying is twofold:

  • SERIALIZABLE incurs an extra cost, because predicate locks have to be maintained.

  • You have to be ready to repeat every transaction in case you receive a “serialization error”.

Of course, if an application doesn't use transactions at all, there is no way to make it safe...

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562731

To use SERIALIZABLE to guarantee truly serial transactions, every transaction would have to acquire a global lock on all tables in the database. There's no way to know in advance what data your transaction will try to read or update, so a global lock is the only real guarantee.

Both Oracle and MySQL have a transaction isolation level that they term SERIALIZABLE, but they take an optimistic strategy. Though each does so in different ways, neither does a global lock as I described above.

MySQL implements SERIALIZABLE in a simple way: Every SELECT is implicitly SELECT...LOCK IN SHARE MODE (known as SELECT...FOR SHARE in 8.0). This means that if two sessions read the data and then try to update it as shown in the balance debit example in the paper, they'll cause a deadlock because both updates will be waiting for the other to release its shared read lock.

Oracle allows you to read and update data, and acquires locks optimistically (i.e. at the time you read or update). But if you try to update data that has been modified since your transaction began, you get this error:

ORA-08177: can't serialize access for this transaction

In both Oracle and MySQL, the best remedy for the ACIDRain vulnerability has nothing to do with isolation level. The remedy is to avoid the race condition by using explicit locking reads using the FOR UPDATE query option. That ensures exclusive access to the data starting at the time you read the data.

Another remedy would be to issue explicit table-locking commands, such as LOCK TABLES in MySQL or LOCK TABLE in Oracle.

References:

Upvotes: 4

Related Questions