Transaction:A unit of work done in the database like writing a bunch of records into the database.
ACID:A set of properties that a database transaction in a relational database is supposed to have. Let us take the example of a transaction that writes twenty rows into the database with a single insert statement. If this particular transaction needs to adhere to ACID,what are the things that the database needs to ensure.
Atomicity: Atomicity dictates that the transaction should work as an atom.Etymology of “atom” is a- “not” + tomos “a cutting means something which cannot be cut.That means,the twenty records cannot be cut meaning you can’t have five records persisted into the database and fifteen records not persisted into the database.Either all twenty records get persisted or none.
Consistency:The word meaning of consistent is being in a state of agreement or harmony.Now,if we apply that meaning to the context of a database system,it basically would mean that once the transaction is complete,the database should be in a consistent state,a harmonious state,a state that is in line with the rules that have been defined in the database.Where are the rules defined in the database?The rules are defined in foreign key constraints,in primary key constraints,in indexes.The rules also include the triggers associated with tables meaning if an insert into table A has a trigger defined that is to cause an update in table B,then we want the update in table B to also happen and if the database is unable to update table B for whatever reason,then the database is expected to fail the insert into table A as well.
Isolation:Isolate means “to keep apart”.When a transaction needs to be kept apart,what can that mean?That other transactions cannot tamper with it’s data.That it needs to be protected and isolated to be able to carry out the direct and indirect responsibilities that come with performing a transaction.
Durability:Durable means “lasting,permanent”.So when a transaction is durable,what would that mean?That the work that the transaction has done would stay.Does that mean another transaction cannot later modify the data?No,it does not because that would question why this transaction was allowed in the first place.Durability indicates that the data should be protected from server crashes,power outages etc.When the power comes back up,you still have the data that you had saved in your transaction before the power had gone off.
CAP Theorem:Given the paradigms of Consistency(where consistency means that every read would return the latest information from the database or an error) and Availability(That every request must receive a non-error response) in a Partition-Tolerant environment (where Partition-Tolerance is the ability of a system with multiple nodes to continue to fulfill the system’s function even when arbitrary number of messages fail in communication between the nodes in the system),it is possible to guarantee only one of Availability and Consistency.
For instance,if an online store had ten books in stock and these can be purchased from two different locations managed through application pieces running on servers at locationOne and locationTwo,then partition tolerance would require that serverOne and serverTwo do a hand-shake before letting userOne at locationOne to deplete stock from the inventory.In this scenario,if the communication link between serverOne and serverTwo is broken,the system can make two choices — One,to tell the user that currently communication within the system is not hundred percent and hence I am unable to let you buy books.This essentially amounts to the system not being available to the user while the network communication is down.However,this allows the system to remain consistent and the inventory represented in the system continues to be accurate.The second choice is that the userOne is allowed to purchase the book even when the communication to serverTwo could not be done by serverOne to ensure that the inventory is indeed accurate and that serverTwo has not depleted inventory.The advantage here is that the system is Available and the user does not face an issue with accessing the website.However,this would mean that the system is acting in a sub optimal state meaning it is playing blind.For instance,say if at the same time,a userTwo had purchased ten books through serverTwo,then effectively the system needs to fulfill eleven books while only ten books are present in the store.
Isolation Level:When you look at isolation,there is various degrees of isolation.For instance,if you have to work in isolation to be able to concentrate,you might want to switch off your phone and sit in a closed room which would be one level of isolation versus you just switch off the phone and you don’t mind sitting in the company of other people who are doing their own work versus you have your phone switched ON as well, you have just decided that you are not going pick any calls while your work is in progress.Analogous degrees of isolation apply to database transactions.
Lower isolation level: in a database encourages more concurrency while also increasing the risk of data corruption due to concurrency effects.
Higher isolation level:in database discourages concurrency but increases the potential of locking out other transactions while this transaction is in progress.
Higher isolation level favors Consistency and discourages Availability while lower isolation level favors Availability but harms Consistency.It is hence a choice of the business to decide what level of isolation is needed for the particular system at hand.
While isolation level is a degree of how isolated your transaction is,it is consequently also a degree of how soon the changes done by your transaction is visible to other transactions.
Serializable Isolation Level:This is the highest level of Isolation.This requires both Read lock and Write lock to be able to read or write data that this transaction is acting on.Also,Range lock would be required for Select statements executed against a range.
Repeatable Read Isolation Level:This is one step below the Serializable Isolation level.The difference here is that Range lock is not managed and so Phantom reads can occur with this isolation level.
Phantom Read:This is said to occur when two instances of the same select done in one transaction returns different results as another transaction inserted data between these two reads.The extra data that “showed up as a surprise” is what is referred to as Phantom here.
Read Committed Isolation Level:The word meaning here is that the data that a Read operation gets is committed data.However,if two instances of the same read are done in a transaction,it is possible that the data returned is different as the rows are not locked for the duration of the transaction.
Read Uncommitted Isolation Level:This allows a transaction to read uncommitted data.That is transactionOne reads rows inserted by transactionTwo which has not yet been committed.So,it is possible that transactionTwo rolls back later causing the rows to be removed but transactionOne would be unaware of this.This is the lowest level of isolation.
Non-Repeatable Read:This is in context to a particular row.This means that if you repeat the read on the row,the data could have changed.
Phantom read,Non-repeatable read and dirty read are classified as read phenomenon and Serializable,Repeatable Read,Read Committed and Read Uncommitted are isolation levels.Stricter the isolation level,less the number of categories of read phenomenon that may occur.
(Serializable())
(Repeatable Read(Phantom))
(Read Committed (Phantom Non-repeatableRead)
(Read UnCommitted (Phantom Non-repeatableRead DirtyRead))
Operations:Fundamental operations in a transaction are
1) Write
2) Read
3) Range(for select）
Lock Duration
Indicates for how long the lock is held.
Lock can be held for as long as the transaction is committed (denoted by ‘C’) or while the statement is executing(denoted by ‘S’)
Isolation levels and Lock Duration
Stricter the isolation level,more the number of operations that would need a ‘C’ lock or commit lock.More important the operation,more the need to lock it with a ‘C’ lock which would be Write > Read > Range.
Serializable isolation level would use ‘C’ lock for write,read and range operations.
Repeatable Read isolation level relaxes range to use ‘S’ lock and ‘C’ lock for Write and Read.
Read Committed isolation level relaxes range and read to use ‘S’ lock but uses ‘C’ for Write operation.Since,the Write operation is done using ‘C’ lock,the write gets committed properly and that is the reason this lock is able to read committed data.However,since Read operation is not ‘C’ lock,it cannot ensure Repeatable Read.
Read Uncommitted isolation level relaxes range,read and write operations to use ‘S’ lock instead of ‘C’ lock.So it is open to all issues of Dirty Read,Non-Repeatable Read as well as Phantom Reads.
Lock: is applied on a database row or a page by a transaction.
If a transaction applies write lock on a row,then only that transaction is able to write data on to that particular row.
If a transaction applies read lock on a row,then other transactions can be prevented from reading this particular row while it is being updated.The reason is that the transaction is changing the data on the row and it might make sense to prevent other transactions from accessing data that is about to be obsolete.
Locking methodologies:The two locking methodologies used are Pessimistic locking and Optimistic locking.With pessimistic locking,the lock is applied on the record or page as soon as the lock is requested.With optimistic locking,the lock is applied only when the record or page is being updated.
With pessimistic locking,it is guaranteed that the row will be updated but with optimistic locking,it is not guaranteed.If another transaction tries to update the same record,then the commit step for this transaction would fail.
Lock Conversion: is the process of changing the locking mechanism from Optimistic locking to Pessimistic locking and vice versa.
Lock Downgrade: is the conversion of the locking mechanism from Pessimistic locking to Optimistic locking.
Lock Upgrade:is the conversion of the locking mechanism from Optimistic locking to Pessimistic locking.
What is the mechanism of Optimistic Locking?
Locking is in reference to a database record being locked by a transaction.If the database is using the Optimistic Locking strategy,then it does not lock the record when the record is first read.Instead,the state of the record is saved in the transaction and later on at the point when update is about to be made,the transaction checks if the record state is the same and if yes does the update.If the state of the record at the point the database is about to do the commit is different from the state of the record that was read initially,then the transaction realizes that another transaction has come in and updated the record and hence rolls itself back.
What is the mechanism of Pessimistic locking?
As soon as the transaction needs to access the record,the record is locked so that no other transactions can work on the record.This ensures that no competing update goes into the record.Pessimistic locking is hence a fail-safe technique.However,Pessimistic locking can cause records to be locked when it is not needed and lead to lock contention issues.
