3

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?

1 Answer 1

5

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.

4
  • 1
    1)it should be avoided to read the same record twice within the same transaction. What if a record is updated by the transaction. If it is not read-back later an update on the record can be missed 2) indicate the write intend on the initial read by requesting a write or update lock. Is this done via SQL? Could you give an example?
    – Cratylus
    Oct 13, 2013 at 21:45
  • 1) If you updated the record, you should know what you wrote. If someone else updated the record you have a problem. See 2) for one technique to prevent that. <br /> 2) see my update to the answer. Oct 13, 2013 at 22:52
  • 3
    Your answer is too long and incoherent. Serializable is the answer to the question. Don't rely on developers handling it in software. The ACID compliant RDBMS knows how to handle things. I guess handling money using MySQL should be outlawed :/
    – Philᵀᴹ
    Oct 14, 2013 at 0:35
  • 1
    @Phil:Why?Is another DB better for this?
    – Jim
    Oct 14, 2013 at 5:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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