What is the standard isolation level for a db used for money data?

Question

When using a database server for monetary/financial data, I can only assume that using transactions is mandatory.
But what I am not sure is which is usually the isolation level? Is a READ-REPEATABLE used? Or only a serialized level?

Answer 1

The write behavior of all transaction isolation levels is the same. However, they are very different when it comes to reads, particularly repeated reads of the same data within a transaction. There are three main behaviors that you need to be aware of:

1. Dirty Reads
2. Non-Repeatable Reads
3. Phantoms

Wikipedia has a good explanation of these behaviors: http://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_Levels_vs_Read_Phenomena

You need to figure out which of these behaviors your application can handle. If it is an absolute requirement, that non of these can occur, than you have to use the Serializable isolation level. However, this is also the level with the least amount of concurrency, as preventing these phenomena requires a great deal of serialization of write processes. (Hence the name.)

In many situations you can get away with less restrictive isolation levels provided you use good coding practices. For example, in general it should be avoided to read the same record twice within the same transaction. That way you don't need to worry about non-repeatable reads.

However, sometimes you need to do things like check if a record exist and create it if necessary. This two step process is only safe when using the serializable isolation level as it is the only one that allows range locks and with that gives a way to prevent inserts into a table area that another transaction has "looked at".

A financial application probably has many situations where one record is read and then based on that one or more records including the original one are altered. Every tim you encounter a situation like that, it is more important that you indicate the write intend on the initial read by requesting a write or update lock. If you do that within the same transaction that executes the writes later on you are not dependent on the isolation level at all. You also will reduce the likelihood of deadlocks.

In the end, the choice of isolation level really depends on your requirements. The type of application is not a good guide to pick an isolation level. Look at each requirement and see if the implementation requires a specific isolation level.

---

Below is an example of how to use the FOR UPDATE hint in MySQL to request an update lock during a select statement:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE accounts(id INT, balance INT);

INSERT INTO accounts(id,balance) VALUES( 1,42);
INSERT INTO accounts(id,balance) VALUES( 2,16);

GO

CREATE PROCEDURE swap(
  pId1 INT,
  pId2 INT)
BEGIN
  START TRANSACTION;

  SELECT @Balance1 := balance 
    FROM accounts
   WHERE id = pId1 FOR UPDATE;

  SELECT @Balance2 := balance 
    FROM accounts 
   WHERE id = pId2 FOR UPDATE;

  UPDATE accounts SET
    balance = @Balance2 WHERE id = pId1;

  UPDATE accounts SET
    balance = @Balance1 WHERE id = pId2;

  COMMIT;
END;

Query 1:

CALL swap(1,2);

Query 2:

SELECT * FROM accounts

Results:

| ID | BALANCE |
|----|---------|
|  1 |      16 |
|  2 |      42 |

Note, this is an example of how to implement the FOR UPDATE hint. It is not necessarily the best solution to solve the demonstrated problem.