Relational Database Transactions Primer for a S/w Developer

With special focus on MySQL (InnoDB)

Ques: Why do we need to understand transactions, isolation levels, and locking etc ?

- Transactions are basically a group of logically-related query statements. Generally, this group of statments
belongs to one API call / one controller action. They can also be a group of DML (Insert, update, delete)
queries, where if one of them fails, we need to undo rest of them, or retry the failed item again etc.

For eg: AddOrder is a Transaction (gets products and its details, update their stock, add the order etc).
Similarly, AddItemToCart (gets products and details, gets Cart details, checks if stock already there or not,
updates or inserts or ignores the cart)

- A (Atomicity) in ACID: Each transaction is considered as one unit and either runs to completion or is not
executed at all. Any failure should rollback everything else.

For eg: Seller is generating an invoice. Now, first we will check whether the same invoice number already
exists or not. If not, then we create the invoice entry. And, then we mark the order products which are in the
invoice. If the marking step fails, then we will have a useless invoice entry with no products in it. So, we
need to rollback everything and show failure to try again. If we don’t rollback the invoice entry, then we also
have wasted an invoice number, which cannot be reused now, due to duplicate constraint.

- Also, when we are building a concurrent high-scale system, that means multiple sessions may be trying to
modify same data at same time. This can lead to data corruption and confusion in different locations. So, we
need to realize when to use transactions effectively. However, transactions also slow down the system a bit,
so we also need to be aware when to avoid (or optimize) them, or when to downgrade their strictness.

Ques: What type of problems we are trying to solve ?

- Three types of reading problems can occur when two transactions (T1 and T2) are working on same
row(s) of data. Note that these are reading problems only (occur only when we have SELECT also in group
of statements, they are not applicable when all you are doing is DML queries only):

i) Dirty Read - T1 updates a data and has not committed it yet. Meanwhile, T2 reads the uncommitted but
updated data of T1. However, due to some issues at T1, it rolls back. Now, T2 has read unexisted data.

For eg: A customer is placing an order and selects an item which has only one quantity left. His transaction
T1 reserves the quantity; Meanwhile another customer is looking at same product, and finds it out of stock.
However, T1 fails and quantity is back in stock. But T2 still considers out of stock.

ii) Non-Repeatable Read - T1 reads a data; meanwhile T2 updates the same data. T1 reads the same data
again, but finds that data has changed (non-repeatable).

For eg: T1 is placing an order and gets price of the item. Meanwhile, in T2, seller updates the price. T1 reads
the price again for some other calculations and now there are severe mismatch issues.

iii) Phantom Read - Same as Non-Repeatable Read, but it has different number of rows (which can have
same or different data).

For eg: T1 gets list of Orders in Pending status. Meanwhile, in T2, another customer places another Pending
Order. Now, in the same session, T1 again looks for Pending Orders, but now has more Orders than
previously found.
Ques: What does Isolation Level (I in ACID) represents then ?

- Isolation Levels are basically guidelines, specified in SQL standard, to solve the various reading problems,
as discussed in the previous question. These 4 levels are of different severity levels, with the lowest level
allowing some problems, while highest level not allowing any of these problems at all.

- When writing a transaction, we need to be aware of which level to be used, to find a balance between
performance and reliability/consistency. Higher the level, slower the query would be, but it will provide
more consistency. Moreover, if your transaction is DML-only, then you may set the Isolation level to lowest.

i) READ UNCOMMITTED – As name suggests, a transaction can read even the uncommitted data of
other transaction. So, all the three problems (Dirty read, non-repeatable and Phantom) can occur.

ii) READ COMMITTED – A Transaction can read the committed data of other transaction, so Dirty Read
will never occur. However, Non-Repeatable and Phatom can still occur.

iii) REPEATABLE READ – It maintains the same version of data, which it read for the first time in the
transaction. Except, if the data is updated in the same transaction, then it shows the new data.

iv) SERIALIZABLE - Safest. But this affects performance. As it is like a serial connection; only one
session can work at a time, on the table/row.

Ques: What is Locking, MVCC, Undo Log, Deadlocks etc in MySQL specific terms ?

- InnoDB only supports Transactions. Moreover, MyISAM has been deprecated in MySQL 8.0 onwards.
- Default setting of Isolation Level in InnoDB is REPEATABLE READ
- Another thing we need to understand that without explicitly specifying a transaction, every DML query in
itself is Atomic in InnoDB. Normal SELECT queries don’t do any locking.

- InnoDB uses various types of Locking concepts ( [Link]

- There are two basic types of Locks. S (Shared) and X (Exclusive) locks. Multiple sessions (transactions)
can acquire S-lock simultaneously on same data, and can read simultaneously. However, in case of X-lock,
only one session can take that lock for writing purposes (others wait).

- InnoDB can lock Indexed rows, Non-indexed rows (using hidden clustered index), Gaps-locking, Next-key
locking, etc to handle numerous scenarios.

For eg: T1 fire a query: SELECT * FROM table WHERE id > 100
Now, if you have the following two rows in table, id = 90 and id = 102. But no rows, between them. The
query will return the id = 102 row. Now, if we lock only id = 90 and 102 row, there is a chance that another
session T2 can insert a row between this gap (eg: id = 101). Now, if T1 runs the same query again, it will get
id = 101 and 102 rows (Phantom Rows). InnoDB solves this using Gap Locking in the default Isolation
Level itself (REPEATABLE READ) . However, there is a performance cost for this.

- Another concept is Multi-Version-Concurrency-Control and Undo Log, which is used to maintain various
versions of changes performed by various transactions. Basically, instead of storing the new data
(uncommitted) by a transaction, it simply stores the delta change done. This is how REPEATABLE READ
minimizes locking issues; however, it also consumes RAM buffer, disk spills issue, and is less performant.

- Deadlocks: Happens due to Write queries (only), when multiple sessions are trying to get lock on same
data. For eg: T1 takes S-lock on table A, and is going to take S-lock on table B. Meanwhile, T2 has already
taken an X-lock on table B first, and is now wanting to get X-lock on table A, but deadlocked.
 SUMMARY (Thumb Rules to Follow):

- Change default Isolation Level to READ COMMITTED first, at your Server level. It is the default setting
in other RDBMS (eg: Oracle etc). In general, we don’t need to requery the same set of data in same session.

- For every activity (group of SQL queries), determine if the concurrency can cause problems or not. Even if
there can be read inconsistency, check if that much is acceptable or not.
If yes, avoid Transactions, so that performance is better. If no, then try to minimize number of queries to be
fired. Design the process such that you don't select a specific data again and again (in some very specific
cases, we need to, but in most cases, we dont). Try to put your DML queries (Update, Insert, Delete) at the
end of transaction. Keep transactions short and simple.

- Generally, In most of the reporting APIs, you dont really need to worry about transactions at all. Infact, you
can even downgrade to READ UNCOMMITTED , if the reporting is just to give a sense of trend, and does
not involve accurate accounting.

- You should still use transactions, when there are a bunch of connected DML queries to be run, so that any
failure ROLLBACK s other Insert/Update/Delete done in other tables.

- Only and only, if you really need to read the same data again, and it is necessary that it should be consistent
as the first read, then SET Isolation level to REPEATABLE READ for that transaction. Using SET
TRANSACTION statement, we can set the isolation level for a specific session.

- Use START TRANSACTION or SET autocommit = 0 to start a transaction. But dont forget to close the
transaction by COMMIT / ROLLBACK , other transaction will be left open, and there will be locking issues.
If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL
rolls back that transaction. Also, your application must be coded to handle deadlock and reissue queries
if needed.

- If you query data and then insert or update related data within the same transaction, the regular SELECT
statement does not give enough protection. Other transactions can update or delete the same rows you just
queried. In that case, Use Pessimistic Locking scenarios such as SELECT .. FOR UPDATE or SELECT ..
FOR SHARE (Ref: [Link] )

For eg: FOR SHARE allows multiples sessions to read the same row simultaneously, but no one can modify
it. So, it is good for the case when you want to ensure that the data which you just read can be read by others,
but not modified by any other. Eg: Price of the product at the time of placing the order. If seller is wanting to
change the price at that time, then it will have to wait until the order is placed.

For eg: FOR UPDATE does not allow other sessions to even read. For eg: you want to update the stock
quantity at the time of placing the order. So, you get the current quantity, lock it; place the order, and then
update the quantity.
Note that in this example, there are other better solutions also, like using UPDATE stock directly, then place
the order. If any issue, everything rolls back.

Another possible example is you want to increment the order number counter. Now, if two sessions
simultaneously reads the same counter, then both of them will have same order no. Hence, we instead LOCK
it FOR UPDATE , and get the order number, increment it and then update.

- Use statements like UPDATE .. WHERE .. (SELECT ..) , INSERT INTO .. SELECT .. etc to minimize
number of queries (if you can).

- Not a immediate recommended reading, but in case you are looking to optimize performance at deeper
levels, you can read [Link] to figure out how
various queries lock in different scenarios. WHERE conditions also play an important role to figure out how
much locking will be done. At the end of the day, our goal is to have minimum Locking with No data
inconsistency, and less buffer usage.