Perfect For Beginners!
Free Trading Education!
Free Demo Account!
Get Your Sign-up Bonus Now!
Good Broker For Experienced Traders!
SQL Transaction Isolation Levels Explained
This article is part of a series. You do not have to read them in order but I will be referring to topics and explanations in previous articles:
Before I can explain what’s different about the isolation levels we have to understand what it is that can go wrong with concurrent transactions (more than one client viewing or modifying the data at the same time).
Let’s say we have a table users :
A dirty read is when the current transaction reads a row written by another uncommitted transaction that’s currently in-flight. For example:
Basically, if the database is not able to keep track of who is changing the data (by keeping multiple versions of the same row with different visibilities) then rows be read even through they should not yet be visible to that other transaction.
A non-repeatable read occurs when the current transaction reads the same data but this time it is different. It is different because another transaction has been committed during the life of the current transaction:
Basically, the database does not maintain what the transaction has already seen so each time the data is read (such as multiple SELECT statements in the same transaction) the same visibility check is done on those rows but some rows may have changed in the mean time.
A phantom read happens when the current transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
This can be thought of as a special type of non-repeatable read, but it is important to distinguish it as a different case because the possibility of it occurring depends on the isolation levels explained next.
The distinction is that that the original rows are re-read correctly (even if they had been changed) but a new row (or rows) have been inserted into the range it previously selected. So it hasn’t broken the re-read rule, but the data returned is still different.
The SQL Standard
The SQL Standard defines four isolation levels which are described by what type of errors they are allowed to permit. It does not specify how the implementations themselves work.
Perfect For Beginners!
Free Trading Education!
Free Demo Account!
Get Your Sign-up Bonus Now!
Good Broker For Experienced Traders!
In most databases MVCC is used. Sometimes with some complex rules to handle higher isolation levels built on top. However, there are other mechanisms; SQLite3 use a separate journal, for example.
- Read uncommitted permits dirty reads, non repeatable reads and phantom reads.
- Read committed permits non repeatable reads and phantom reads.
- Repeatable read permits only phantom reads.
- Serializable does not permit any read errors.
“Read uncommitted” is the most loose transaction isolation and is in fact not even implemented in some databases because it is too loose to be useful. A transaction in this mode is susceptible to all types of read errors mentioned above since it is not required to even check if a row is committed or not.
If the database itself was not able to keep multiple version of a row but you still wanted the virtual container of a transaction to be able to perform a ROLLBACK this might be your only choice.
“Read committed” is the most common transaction isolation and usually is the default as it has the best balance between locking and performance. It will never read versions of rows that are currently uncommitted, however it is still susceptible to other transactions changing data between statements.
“Repeatable read” ensures rows that have been read (rather than modified) will still be read, even if they are changed by other transactions. This provides an almost true snapshot of the data however it does incur overhead with locking and/or causing other transactions to be rolled back more commonly.
In a perfect world (at least for a database) we would like only one client to be connected to the database at a any given time. This would guarantee there are no side effects between concurrent clients, since there are none.
This is “Serializable”, the most strict mode and works exactly like this — client perform transactions in serial.
In practicality a database could not serve just one person at a time so some fancy internal algorithms are used to make sure that clients are not overlapping in anyway. This is tricky but means clients can concurrently work with the database but have the same guarantees as if the transactions were formed one after another.
Depending on the database system it may rollback a transaction at the time of, or at the end of the transaction if it thinks the transaction isolation has been compromised. Applications that need this level of isolation should be built carefully so that they can always retry the transaction on failure.
Here are some of the common database vendors and what levels of isolation they support.
DB2 supports all four database isolation levels, but has different names for each of the levels:
- Uncommitted read (UR) -> Read uncommitted.
- Cursor stability (CS) -> Read committed (default).
- Read stability (RS) -> Repeatable read.
- Repeatable read (RR) -> Serializable.
Derby supports all four levels of isolation, with read committed being the default.
H2 supports only three isolation levels; read uncommitted, read committed and serializable which can only be set at the connection level (not per transaction). The default is read committed.
MySQL and MariaDB
When MySQL and MariaDB are using InnoDB they support all four transaction isolation levels with repeatable read as the default. Even though MySQL and MariaDB allow other pluggable engines, InnoDB is the most common when transactions are required and is usually the default engine on modern versions of either.
The original engine, MyISAM does not support transactions but it still used and available. Transaction commands will be ignored rather than result in an error. This is effectively running a database in read uncommitted mode.
Oracle only supports read committed (default) and serializable.
PostgreSQL supports three levels; read committed, repeatable read and serializable. The syntax allows you to choose read read uncommitted but it will function as read committed.
Read committed is the default.
Redshiftis a modified version of PostgreSQL that can only be used on Amazon Web Services (AWS). Redshift only supports serializable. Selecting a different isolation level will have no effect.
Microsoft SQL Server (which is also commonly used as a backend for Microsoft Access instead of the in-built engine) supports all four transaction isolation levels with the default being read committed.
SQL Server also has a fifth option called “snapshot” which allows transactions to see a consistent read state. It is unclear if this is an alias of another isolation level or has a specific implementation.
The default implementation of SQLite3 (since you can compile it from source with lots of different options) only works in Serialized mode.
Anyone that has dealt with SQLite3 a lot will understand that this can cause a lot of locking issues but it also the most safe safe considering all the mission-critical systems that SQLite3 is deployed in.
Since v3.7.0 write-ahead logging (WAL) has been introduced to make concurrent environments work more like other database vendors.
If your favourite database vendor is not listed, please let me know.
Bitcoin Transaction Explained
Blockchain can modernize the system of mutual settlements and radically change the work with external regulators. The uniqueness of the technology lies in the immutable and irreversible nature of data in a chain created on a cryptographic algorithm.
The main characteristic of any transaction is determinism. This operation can either be performed or rejected, regardless of how many stages the process of its implementation is divided into. A blockchain cryptocurrency transaction is a symbiosis of a bank transfer and the changes made to the information block.
Since Bitcoin is the most popular cryptocurrency in most countries of the world, the transaction of this particular asset will be the subject of our review. How does a Bitcoin transaction work? Why is my BTC transaction unconfirmed? Get the answers in this new Changelly article.
What is BTC Transaction
A transaction is a signed element of the information module necessary for transferring assets between network participants. After the verification is completed, bitcoin miners add txid transactions to the database of the new crypto block.
The Bitcoin network is built on the modern version of a digitized ledger called a distributed ledger. The distributed registry system is a vast number of copies of the database. Any change in the structure of information will be reliable only after the transaction is confirmed by the network nodes. A record of the performance of a particular operation is entered into each of the copies of the database.
Protective mechanisms are built into the project’s blockchain program code to preserve the integrity of the data structure. It is impossible to cancel and delete information from the log of the completed bitcoin transaction.
In addition to standard transactions, which are the direct equivalent of bank transfers, there are also generating ones, during which a certain number of created coins is sent to the crypto miner as a reward for the block found.
They differ only in that the sender of the standard Bitcoin transaction can be any owner of digital coins, and the system executes the generators in automatic mode after a new block passes the test.
Bitcoin Transaction Life Cycle
BTC Transaction Formation
To complete the transfer, the cryptocurrency holder who owns the private key to access the program fills out the sending form in the electronic crypto wallet. It is necessary to indicate the address of the recipient and the amount of funds sent.
When the sender confirms the intention to send the money, the transfer information falls into a special meme pool, where it will wait for its turn to be processed by the miners.
Each of the Bitcoin transactions is sent to all nodes that combine them into a new block. When one of the miners finds a hash code, the block is sent for verification. In a Bitcoin network, a transaction is considered completed after six subsequent blocks are found confirming its validation.
BTC Transaction Example
We will tell you more about the blockchain transaction mechanism. The digital signature of operations in the blockchain system is based on cryptography and has two keys. The first key is private, available only to the owner of the assets, and is kept confidential and is never transferred to other people.
However, on the deposits of trading floors and in some centralized systems for storing electronic assets, private keys or their duplicates are stored by the administration of the service.
The second key is public. It is needed to conduct, verify and track the Bitcoin transaction. It is impossible to calculate the secret key using the public one, but it is not very difficult in the reversed order.
On the Bitcoin network, the ECDSA elliptical cryptography standard is used in conjunction with the secp256k1 elliptic curve. The private key is 32 bytes, the public key is 33 bytes, and the signature is approximately 70 bytes.
Let us explain in simple words the idea of signatures with a public key. Alice sends Bob 1 BTC. She forms a transaction, which indicates where to get the money and to whom to send it to, and confirms her right to dispose of this fragment of the blockchain with a private key. Miners confirm the validity of a financial transaction based on a public key.
Peer-to-peer networks do not provide a central node that controls the operation of the system, which eliminates financial fraud.
How to Track My Bitcoin Transaction
So, the money was sent and left the wallet, but it will take some time until it reaches the recipient. What if something happens to the coins – how to check the Bitcoin transaction? For this, Block Explorer was created.
Verification of Bitcoin transactions is available in the section ‘Search and confirmation of transactions’.
- Open the main page of the site;
- Choose the column ‘Data’;
- In the pop-up menu, open ‘Explorer’;
- To simplify the search, click the ‘All Blockchain’ button and select the ‘Bitcoin service’;
- Enter the transaction txid in the address bar and click “Search”.
TXID is a transaction identification number that allows you to track it in the blockchain system. Do not confuse transaction id with wallet address. TXID is a unique passport that the system assigns to a particular digital asset transfer. The only purpose of TXID is to help any user to detect a transaction and track its status in the crypto network.
At the wallet address, it is quite possible to track the transaction of bitcoin. It will be displayed along with other data in the history of transfers ever sent or received by the owner of the wallet.
Why is My BTC Transaction Unconfirmed?
The size of one block in the Bitcoin network is small, only 1 MB, which protects the system from DOS attacks. With the growth of its popularity, cryptocurrencies have started having problems with scalability. It became increasingly difficult for miners to cope with the processing of a significantly increased number of transfers.
It is impossible to accurately answer the question: ‘How long does it take to confirm a Bitcoin transaction?’. During the cryptocurrency fever, the load on the Bitcoin network was such that the number of unconfirmed transactions reached a critical level. Sometimes it took a week to wait for a transfer from one address to another. However, in 2020, checking a Bitcoin transaction takes no more than an hour and a half.
Network nodes can reject an unconfirmed BTC transaction, and in this case, the coins will be returned to the sender address. The main reason for freezing and returning BTC transfers is a low commission.
Bitcoin clients enable users to set the size of commission payments independently, but the processing fee must be adequate. Otherwise, the transaction will not take place. Although if the number of unconfirmed transactions is minimal, you can transfer with a low commission safely.
How to Speed Up Bitcoin Transaction?
There are three ways to independently solve the bitcoin unconfirmed transaction problem, but note that none of them guarantee a 100% result.
Increase the commission fee (Replace-by-fee). Previously, transactions in the Bitcoin network were carried out with a fixed commission. With the growing popularity of digital gold, the number of online payment transactions has increased.
Then there were utilities with the option of dynamically changing the size of the commission fee, which allows you to speed up unconfirmed blockchain transactions. We are not talking about a real change in the money already sent. By activating the RBF option, you tell the network that you are ready to pay more in case of a delay, and the new transfer is automatically accepted instead of the old one. This feature has Bitcoin-wallets like Bitcoin Core, Electrum and Green Address.
The second method is called Double Waste. No, this is not at all a malicious attack, which will require at least 51% of the total power of the Bitcoin network. When you resubmit the transaction with a higher commission, the old one is cancelled. You should not abuse this method.
The last is Child Pays For Parent (CPFP). It means selecting transactions for mining not just based on their fees but also based on the fees of their ancestors (parents) and descendants (children). Both the sender and the recipient can use CPFP if he has money in his wallet. It is necessary to form another transfer, using the output of a stuck transaction. Note that the balance of funds should be enough to pay the commission for two transactions.
A more accurate explanation of how CPFP works is with a fiat money example. When you buy a product, you must “send” cash to the seller. He also uses this money to purchase other goods while making that “transfer”.
The seller’s transaction will be considered the child of your parental transaction because he will not be able to buy anything until you pay him. If you pay higher than the cost of the goods you buy, the seller must return the difference to you. Each Bitcoin transaction has the same exit for change, allowing you to start the CPFP mechanism.
Accelerating transactions in the Bitcoin network and other cryptocurrencies is one of the priority tasks for the creators of blockchain projects. One option is the Lightning Network, which is used as a blockchain add-on to achieve higher throughput.
There are other exciting developments. Cryptocurrency is the money of the future, and now we live in a period of development of a new type of a world financial system.
Transaction Propagation and Isolation in Spring @Transactional
Last modified: October 20, 2020
I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:
In the 9 years of running Baeldung, I’ve never, ever done a “sale”.
But. we’ve also not been through anything like this pandemic either.
And, if making my courses more affordable for a while is going to help a company stay in business, or a developer land a new job, make rent or be able to provide for their family – then it’s well worth doing.
Effective immediately, all Baeldung courses are 33% off their normal prices!
You’ll find all three courses in the menu, above, or here.
In this tutorial, we’ll cover the @Transactional annotation and its isolation and propagation settings.
2. What Is @Transactional?
We can use @Transactional to wrap a method in a database transaction.
It allows us to set propagation, isolation, timeout, read-only, and rollback conditions for our transaction. Also, we can specify the transaction manager.
2.1. @Transactional Implementation Details
Spring creates a proxy or manipulates the class byte-code to manage the creation, commit, and rollback of the transaction. In the case of a proxy, Spring ignores @Transactional in internal method calls.
Simply put, if we have a method like callMethod and we mark it as @Transactional, Spring would wrap some transaction management code around the invocation:@Transactional method called:
2.2. How to Use @Transactional
We can put the annotation on definitions of interfaces, classes, or directly on methods. They override each other according to the priority order; from lowest to highest we have: Interface, superclass, class, interface method, superclass method, and class method.
Spring applies the class-level annotation to all public methods of this class that we did not annotate with @Transactional .
However, if we put the annotation on a private or protected method, Spring will ignore it without an error.
Let’s start with an interface sample:
Usually, it is not recommended to set the @Transactional on the interface. However, it is acceptable for cases like @Repository with Spring Data.
We can put the annotation on a class definition to override the transaction setting of the interface/superclass:
Now let’s override it by setting the annotation directly on the method:
3. Transaction Propagation
Propagation defines our business logic ‘s transaction boundary. Spring manages to start and pause a transaction according to our propagation setting.
Spring calls TransactionManager::getTransaction to get or create a transaction according to the propagation. It supports some of the propagations for all types of TransactionManager, but there are a few of them that only supported by specific implementations of TransactionManager.
Now let’s go through the different propagations and how they work.
3.1. REQUIRED Propagation
REQUIRED is the default propagation. Spring checks if there is an active transaction, then it creates a new one if nothing existed. Otherwise, the business logic appends to the currently active transaction:
Also as REQUIRED is the default propagation, we can simplify the code by dropping it:
Let’s see the pseudo-code of how transaction creation works for REQUIRED propagation:
3.2. SUPPORTS Propagation
For SUPPORTS, Spring first checks if an active transaction exists. If a transaction exists, then the existing transaction will be used. If there isn’t a transaction, it is executed non-transactional:
Let’s see the transaction creation’s pseudo-code for SUPPORTS:
3.3. MANDATORY Propagation
When the propagation is MANDATORY, if there is an active transaction, then it will be used. If there isn’t an active transaction, then Spring throws an exception:
And let’s again see the pseudo-code:
3.4. NEVER Propagation
For transactional logic with NEVER propagation, Spring throws an exception if there’s an active transaction:
Let’s see the pseudo-code of how transaction creation works for NEVER propagation:
3.5. NOT_SUPPORTED Propagation
Spring at first suspends the current transaction if it exists, then the business logic is executed without a transaction.
The JTATransactionManager supports real transaction suspension out-of-the-box. Others simulate the suspension by holding a reference to the existing one and then clearing it from the thread context
3.6. REQUIRES_NEW Propagation
When the propagation is REQUIRES_NEW, Spring suspends the current transaction if it exists and then creates a new one:
Similar to NOT_SUPPORTED, we need the JTATransactionManager for actual transaction suspension.
And the pseudo-code looks like so:
3.7. NESTED Propagation
For NESTED propagation, Spring checks if a transaction exists, then if yes, it marks a savepoint. This means if our business logic execution throws an exception, then transaction rollbacks to this savepoint. If there’s no active transaction, it works like REQUIRED .
DataSourceTransactionManager supports this propagation out-of-the-box. Also, some implementations of JTATransactionManager may support this.
JpaTransactionManager supports NESTED only for JDBC connections. However, if we set nestedTransactionAllowed flag to true, it also works for JDBC access code in JPA transactions if our JDBC driver supports savepoints.
Finally, let’s set the propagation to NESTED:
4. Transaction Isolation
Isolation is one of the common ACID properties: Atomicity, Consistency, Isolation, and Durability. Isolation describes how changes applied by concurrent transactions are visible to each other.
Each isolation level prevents zero or more concurrency side effects on a transaction:
- Dirty read: read the uncommitted change of a concurrent transaction
- Nonrepeatable read: get different value on re-read of a row if a concurrent transaction updates the same row and commits
- Phantom read: get different rows after re-execution of a range query if another transaction adds or removes some rows in the range and commits
We can set the isolation level of a transaction by @Transactional::isolation. It has these five enumerations in Spring: DEFAULT, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE.
4.1. Isolation Management in Spring
The default isolation level is DEFAULT. So when Spring creates a new transaction, the isolation level will be the default isolation of our RDBMS. Therefore, we should be careful if we change the database.
We should also consider cases when we call a chain of methods with different isolation. In the normal flow, the isolation only applies when a new transaction created. Thus if for any reason we don’t want to allow a method to execute in different isolation, we have to set TransactionManager::setValidateExistingTransaction to true. Then the pseudo-code of transaction validation will be:
Now let’s get deep in different isolation levels and their effects.
4.2. READ_UNCOMMITTED Isolation
READ_UNCOMMITTED is the lowest isolation level and allows for most concurrent access.
As a result, it suffers from all three mentioned concurrency side effects. So a transaction with this isolation reads uncommitted data of other concurrent transactions. Also, both non-repeatable and phantom reads can happen. Thus we can get a different result on re-read of a row or re-execution of a range query.
We can set the isolation level for a method or class:
Postgres does not support READ_UNCOMMITTED isolation and falls back to READ_COMMITED instead. Also, Oracle does not support and allow READ_UNCOMMITTED.
4.3. READ_COMMITTED Isolation
The second level of isolation, READ_COMMITTED, prevents dirty reads.
The rest of the concurrency side effects still could happen. So uncommitted changes in concurrent transactions have no impact on us, but if a transaction commits its changes, our result could change by re-querying.
Here, we set the isolation level:
READ_COMMITTED is the default level with Postgres, SQL Server, and Oracle.
4.4. REPEATABLE_READ Isolation
The third level of isolation, REPEATABLE_READ, prevents dirty, and non-repeatable reads. So we are not affected by uncommitted changes in concurrent transactions.
Also, when we re-query for a row, we don’t get a different result. But in the re-execution of range-queries, we may get newly added or removed rows.
Moreover, it is the lowest required level to prevent the lost update. The lost update occurs when two or more concurrent transactions read and update the same row. REPEATABLE_READ does not allow simultaneous access to a row at all. Hence the lost update can’t happen.
Here is how to set the isolation level for a method:
REPEATABLE_READ is the default level in Mysql. Oracle does not support REPEATABLE_READ.
4.5. SERIALIZABLE Isolation
SERIALIZABLE is the highest level of isolation. It prevents all mentioned concurrency side effects but can lead to the lowest concurrent access rate because it executes concurrent calls sequentially.
In other words, concurrent execution of a group of serializable transactions has the same result as executing them in serial.
Now let’s see how to set SERIALIZABLE as the isolation level:
In this tutorial, we explored the propagation property of @Transaction in detail. Afterward, we learned about concurrency side effects and isolation levels.
As always, you can find the complete code over on GitHub.
Perfect For Beginners!
Free Trading Education!
Free Demo Account!
Get Your Sign-up Bonus Now!
Good Broker For Experienced Traders!