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
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:
- Dirty Reads
- Non-Repeatable Reads
- 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:
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
| 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.
-
11)
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?– CratylusOct 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
-
3Your 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