Data Intensive application — Chapter 7 Transaction
In a database and a client(application), multiple things can go wrong application crash, several client write at same time, network interruption, reading partial writtend data. There are multiple issue that can happen with database.
For these issue we have been using Transaction like either all ( commit) or none ( abort or rollback). But not all application requires transaction , some might prefer availability or preformace thus rejecting transaction. we will see when to use and when not use in this chapter.
The Slippery concept of transaction
All the relational database and some non-relational database provide transaction. Once nosql database came it started using replication and partitioning and abandon transaction entirely or weaken gurantees. But still transaction has its advantages and limitations, we will see that.
The meaning of ACID
Atomicity, consistency, isolation, durability . Opposite of ACID is BASE that is basically available, soft state and eventual consistency
Atomicity
It defines if a client is trying to multiple writes in the database, either all happens or none happens. In case suppose after few write there is an issue in one of the write , then all the previous written data is undone and nothing is saved.
Without atomicity, you will not be able to know which write has happened and which has not happened from the multiple write in case if failure in between the writes.
2. Consistency
It is basically saying database is in good condition. The state before the operation is same after the operation. eg — balance sheet managed in database, overall credit and debit sum was 0 before operation, then it should be 0 after the operations.
consistency is not managed by database, but rather by the application ( application can always fuck up the data that it send to datastore). Atomicity, isolation and durability are part of database and using these three property applicaiton get the consistency in the data.
3. Isolation
It just says two transaction executing concurrently on same data should be isolated meaning only one transaction should execute at a time.
without isolation , there can be data corruption. eg — suppose 2 users user1 and user2 are increasing a value with 1 and initial value is 10. if there is no isolation, both will read at same time and final value will be 11, but it should have been 12. with isolation, it will be 12.
4. Durability
Meaning whenever the transaction is committed meaning your data is stored. Then your data should not be lost in near future.
In single node, we write the data to non-volatile storage like hard disk or SSD. ( using WAL for in memory data). But when there is replicated database — we data is written to the replica nodes, then only we consider the data is written.
Single Object and multi object Operations
If multiple write from multiple client is happening , then atomicity and isolation helps. One save from partial write and other from concurrent writes.
When multiple object ( meaning multiple data) is updated in a transaction, then the other concurrent transaction with wait because of isolation of the data.
Single Object writes — here also atomicity and isolation applies. like suppose there is an issue in writing an 20KB json file like only 10KB has been written then atomicity should come in play. Similary if another client tries to read the data when only 10KB is written the it should not allowed as it will give partial data.(Isolation will work here )
compare and set in this operation you compare to see if someone has update the data or not before saving the data.
The need for multi object transaction
When you have multiple co-ordinated data object change , then you require the transaction.
like in relation database, there can be one data in one data , and one in the other table that has foreign key reference and it needs to be updated. Similarly in document based database, there can be scenario in which two document needed to be updated and required together. where there is secondary index, the index needs to be updated and indexes are different object from transaction point of view
Handling error and abort
In transaction, if there there is any violation or some error, whole transaction is aborted . you can retry the operation from the code again in case of transaction failure.
But in some databases like leaderless replication — when there is some issue and data has been written, it will not undo the data. It’s the application responsibility to recover from the error.
Issues in retrying aborted transaction. 1. If the issue is permanenet like some violation or other issue, then it does not make sense to retry. 2. if the issue is because of overload, then retry will further worsen the situation
if transaction actually succeeded,but while giving ack of commit to the application, there is a network issue. so the application will retry the transaction thus performing the same operation twice.
Weak isolation levels
When isolation is required ? if suppose two transaction is trying to access the same data concurrently, then to let it run parallely we require isolation. If the transaction is working with different data, then there is no need for isolation. This concurrency issue is hard to find and recreate.
There are multiple level of isolation like serializable — it says transaction runs as it is running serially but it creates overload on the system and that’s why few application even though using ACID, uses weaker isolation.
Different level of isolation
1. Read Committed
two thing -> when you are reading the data, you will only read committed data ( no dirty read) and you are writing data, you will only overwrite the data that is committed (no dirty writes)
No dirty reads => dirty read is basically if another transaction is able to read other transaction uncommitted data.
So basically, if multiple values are updated in transaction, then none of the values are available for read until the commit has happened. When commit happens, all the values are available for reading. What issue can happen if no dirty read ?- suppose there is some issue in your transaction and you rolled back, but the other transaction has read the uncommitted data(Dirty read), it might process with the data that never existed.
No dirty write — It basically says one transaction should not overwrite the uncommitted data of the other transaction. But even if we did not do dirty write , still the data of earlier committed write is lost, that issue is still there.
Implementing read committed
So basically what happens is , when a transaction is doing write on the database, it basically put the lock on the rows it is writing. So at the same time if another transaction comes, it will wait for the lock and once the first transaction commit , only then the second transaction get the lock.
This may cause issue in response time, also for read also have to wait. What some of the database does it keeps the old value as well while the a transaction is having the lock, if a read query comes it returns the old values, when commit is done by transaction, it start to return the new values.
Read Committed is default isolation level in PostgresSQL, SQL server 2012, oracle 11g and many other databases.
2. Snapshot Isolation and repeatable read
There should not be any read skew — getting different data for same row in same transaction. If you are following read committed, then you can have read-skew or non-repetable read. Suppose you read a value from row1 and in the same transaction when you are application processing is happening, some other transaction updated and committed the value, when you again read the value — it will changes , even though you are reading the committed value. We need to prevent this and have repeatable read.
The same issue can happen with backup and analytics query ( in case of multi object transaction) — when you are reading the data for backup, at that time only a transaction modifies the value — you might read wrong data of different data of object 1 and 2 (considering object1 and 2 are correlated)
Snapshot isolation is the most common approach for this solution — you create a snapshot of the database and transaction reads from that snapshot only. The snapshot is created at the start of your transaction. Snapshot isolation is good for long running read only analytics query or bacup of the data.
Implementation of snapshot isolation
write happens same as read committed one, locking the row and other transaction will wait for the commit to happen, then it can get the lock.
while reading it follows, multi-version concurrency control(MVCC) — the database keeps multiple version according the write that is happening by different transaction. This keeping of multiple version is MVCC. Here transaction is ever increasing, each version is merked by the transaction that it updating it.
Whenever a read happens — the DB makes a note of all the transaction that is in progress, you do not read the values from those transaction, you do not read the values from transaction higher than you ( transaction is ever increasing), any write made by aborted transaction is ignored.
How index is managed in snapshot isolation ? — PostgreSQL see if different version of the index can fit in same page in B-Tree and in another approach a new page is appended in the B-Tree and it is managed by the background compaction and garbage collection.
Repeatable read and naming confusion
Snapshot isolation is useful isolation for read-only transaction. Many database implement it by different name — like PostgresSQL and mysql by the name of repeatable read. That’s why there is confusion of repeatable read
PostgreSQL uses Snapshot Isolation even when it reads repeatable read. MySQL (InnoDB) default is repeatable read but it also uses MVCC ( snapshot isolation implementation also uses MVCC), MYsql server use locks not MVCC — it basically locks the row that it is reading.
In MVCC rows are not not locked, you are reading from snapshot. But in Mysql Server rows are locked.
Preventing Lost Updates
There are scenario , when two transaction have read the committed value, those two transaction want to save back the data. In this case one of the data is lost. This is basically lost update. This generally happens with read-modify-write cycle. How to prevent it ?
Atomic write operations
It basically says do not read the data , just do the operation in single cycle . In read-modify-write cycle you do three things like reading , the modifying in application and then writing back to DB, But in atomic operation you just say increase the value by 1 , there is no read — it will directly update the value.
Atomic operation generally takes exclusive lock on the object and does the operation.
Simple increment of value or modification of json in mongoDB can be performed in atomic operations, But not all the operation can be done by atomic write like two user simultaneously updating wiki page.
2. Explicit locking
It basically says taking the lock of object for whole read-modify-write operation, so that the other transaction has to wait for the object availability to do the operation. ( if you have seen FOR UPDATE getting used in query, that is locking all the rows returned by the query till transaction is not committed)
3. Automatically detecting lost updates
It basically when the write conflict occurs on a data , you basically fail the transaction doing read-modify-write and ask the application to retry the operations.
It can be achieved by snapshot isolation, as it already have the snapshot version, that can be used to know the conflict. ( most database can perform this operation quite efficiently).PostgreSQL repeatable reap, Oracle’s serializer, SQL server snapshot isolation can do this, but Mysql(innoDB) does not detect lost update.
4. compare and set
This is for database that does not provide transaction, you basically compare the previous value before writing to the DB. ( eg — content = new_content where content = old_content). If database allows to read from old content, then this does hold true and it will give lost writes
5. conflict resolution and replication
in replicated databases, with single leader configuration you can do lock and comapre-set but not on multi-leader and leader-leader less replication.
As we discussed earlier let the concurrent write happen in the database and resolve later in application while data is read or some background job.
Atomic operation are also good in case of this scenario and it resolve the conflict. Still LWW (last write wins) is the default in many replicated DB — here data is lost.
Write skew and Phantoms
write-skew — It is neither a dirty read or a lost update. It is like if two transaction read same object but each transaction update different object ( suppose you are aggregating a column count and it can not go less than 1. It’s value is 2 , suppose both are decreasing a count by 1 and its value can not go less than 1, but if both transaction commit in case of snapshot isolation, both transaction can start with overall count as 2 — its value will become 0)
atomic operation will not work as different object is updated, detection of lost update will also not happen as object is different.
one approach , is put the lock on all the rows that can be affected and do the operation ( use FOR UPDATE first with selecting all the rows intransaction)
more example — meeting room booking
Get Adityaraj’s stories in your inbox
Join Medium for free to get updates from this writer.
Phantoms causing skew writes
Phantom is basically write in one transaction changes the result of query in another transaction. To escape phantom , you use serializable-isolation that we talked about in write skew , basically lock all the rows that can change the result of the query and then do the operation. Other transaction will wait and will not be able to update any rows of the query.
Serializability
Serializable isolation is one of the strongest isolation, if two transaction are runnning conccurenlty, it would be like they are running serially. There will be no race condition, no conflict.
There are three approaches for serializiation isolation
1. Literally executing transaction in serial order => actual serial execution
2. Two phase locking
3. serializable snapshot isolation ( optimistic concurrency control technique)
Actual serial execution
It’s main logic is to use a single thread, no chance of concurrency. Transaction will come and execute in serial order. It was introduced in 2007 only , why ?
main reason is RAM getting cheaper and the few requirement data can be present in-memory, so faster execution. Also OLTP — transaction queries were only making small read and write considered to analytics query.
eg — Redis , VoltDB, Datomic are single threaded execution. In some scenario, single threaded execution perform better than concurrent execution as it can avoid overhead of locking.
Encapsulating transaction in stored procedure
In current scenario most of the transaction last very small time like on http call. Make a http call get the data , then make another call for another process. There is too many calls to the Database, there creates too much time in network.If you database is single threaded and not concurrent then you are fuged.
so you create a stored procedure and submit that to the database ahead of time, and you can use store procedure to get the data. Store procedure is quite fast with any network or disk I/O as data is in-memory.
Pros and cons of store procedure
Code written in database is difficult to manage. harder to debug, test and integrate with the metrics
t database vendor has its own language and it becomes ugly without any library support. A bad store-procedure can hamper the whole database
These issue has been resolved in modern database as VoltDb uses java or groovy, Redis uses Lau. It depends on your use-case.
=> you can partition the data onto multiple node, in case you want to use multiple CPU and extend the write. But be aware of cross partition queries.
Two phase locking
=== it is not 2 phase commit ==== it is different
In snapshot isolation, one transaction blocks other when there is write going on, but the other transaction can still read the data from snapshot. But in 2 phase locking you block even if someone is reading, block if someone is writing. Here you can not read from snaphot, if someon has blocked, means you need to wait for that transaction to read the data.
Implementation of two-phase locking
It is just getting the lock, But there are two kind of locks <<shared locks>> and <<exclusive-lock>> . If a transaction has to read. it takes shared lock, other transaction can also the shared lock on the same row in case it also has to read only. In case if a transaction want to write it needs to have exclusive lock. Once exclusive lock is taken, no other transaction can take the lock either shared or exclusive until the exclusive lock is release post Commit or abort.
Race condition can also ,in case two transaction is waiting for locks ( if one transaction want to update the lock from shared to exclusive after reading the data and some other transaction also want the same thing — then neither will release the lock and it become <<deadlock>>). When dead lock happens, one of the transaction is killed by the database.
2 Phase locking is used in serializable isolation level in Mysql(InnoDB) and SQL server
Performance of 2 phase locking
the main downside is the performance — transaction throughput and response time of the query — it is worse in in 2PL.
one bad query containing lock on multiple rows can affect other transaction and delay it. That’s why 2PL has unstable latencies, high latency in high percentile.
deadlock is more frequent in 2PL than the other isolation and this causes significant wastage effort.
predicate lock => shared/exclusive lock is lock on a single object. But in case you require lock on multiple rows ( like lock for a select query), then you use predicate lock. In this also if you want to read, then it is shared-predicate lock , in case of write it is exclusive predicate lock.
Index-range locks
the issue with predicate lock is it locks too many objects to have serializability.
In index-range lock, you basically locks the data and also its matching index. suppose you are booking a meeting room 123, here index is room_id, so basically have a shared lock on the room id. Or if you are booking from 1 to 3 and there is index on start and endtime, you just have shared lock on start and endtime matching your query.
=> Index-range lock is not as precise as predicate lock , but seeing the overhead in predicate lock , index-range lock is a good compromise.
Serializable Snapshot Isolation (SSI)
Till now we have worked with pessimistic concurrency control. Like 2PC or serial execution. Those are pessimisit, if there is some issue, they just stop the other transaction and this is pessimistic. But Serializable Snapshot Isolation works on optimistic approach, instead of blocking it lets the both the transaction execute and in the end, before the commit it check if something bad happened with the existing data, if it does , then it revert back the transaction.
It performs better than other pessimist isolation if contention is not too much, but if the contention is high, then there is too much abort of transaction and SSI performs bad.
Decision based on an outdated premise
It is just that in what scenario you abort the transaction. There are two scenario
1. Detecting the read of stale MVCC ( multi-version concurrency control) — here different version of data is kept in the database, just before commiting the changes, you see if someone has updated the data ( with the version you can know). In that case, you revert back the transaction.
2. Detecting write that affect prior read — In this if some other transaction has read the data you are trying to write, so that transaction will notify before write that your read data is outdated and while commiting you revert back your write. Here you don’t block the other transaction, but rather you compare in the last.
Performance of serializable snapshot isolation
SSI keeps the data of each transaction , that can be bookkeeping those data can be overload of the database.
unlike 2PC locking , SSI don’t block other transaction for write. It make the latency quite predictable and less variable.
It is just the rate of abort highly affects the overall performance of the SSI.
