Unfortunately, that assumption is not correct when using read committed. So why is this weird? Many people expect that all 1,000 rows will be returned every time AND that the structure of the table or the version of SQL Server should not make any difference. It’s easy to reproduce on SQL Server 2016/2017 and can be reproduced in all earlier versions with a single configuration change (more details in a moment).Īdditionally, if the table has a clustered index created, 1,000 rows are returned every time, on all versions of SQL Server. This behavior had also been reported on earlier versions of SQL Server as well. Furthermore, depending on when the “Batch 2” select is started, during the 10-seconds that “Batch 1” executes, “Batch 2” returns different numbers of rows.
The ‘weird’ behavior is that when the “Batch 2” select completes, after having been blocked by the “Batch 1” transaction, it doesn’t return all 1,000 rows (even though “Batch 1” has completed). Insert 1,000 rows into the table, with a 0.01 second WAITFOR DELAY between each insert.Batch 1: In one SSMS window, do the following (which takes 10 seconds to run):.The problem was occurring on SQL Server 2016 and used the default isolation level of read committed. NEVER throws an exception if there is an active transaction.A while back I was involved in an email thread where people were wondering about some ‘weird’ behavior from SQL Server. NESTED creates nested transaction when there is an existing transaction already or works like REQUIRED if there is no transaction. NOT_SUPPORTED suspends current transaction if there is one. SUPPORTS propagation level uses the current transaction if there is one. MANDATORY propagation uses an existing transaction if there is one. REQUIRES_NEW propagation level says to suspend outer transaction (if there is one) and create a new one. REQUIRED propagation level uses an existing transaction if there is one. Propagation can be set to REQUIRED, REQUIRES_NEW, MANDATORY, SUPPORTS, NOT_SUPPORTED, NESTED or NEVER.Įxample: (propagation=Propagation.REQUIRED) There is also another important parameter of propagation. It performs all transactions in sequence. SERIALIZABLE isolation level prevents all mentioned above side effects. REPEATABLE_READ prevents “dirty” and non-repeatable reads. READ_COMMITTED isolation level has one change in comparison with READ_UNCOMMITTED - it prevents “dirty” reads. With READ_UNCOMMITTED isolation level, we can have all three side effects Default value for PostgreSQL, Oracle and SQL server is READ_COMMITTED, for MySQL - REPEATABLE_READ. Let’s go back to the isolation levels and check their possible side effects.ĭEFAULT value is used when we want to use default isolation level of our RDBMS. Phantom read - one transaction runs the same query twice, but gets a different set of rows as result, because of the changes applied by another concurrent transaction.
Non-repeatable read - one transaction reads the same row twice, but gets different values because between these reads the data was updated by the concurrent transaction. Something wrong happens and T1 is rolled back. Transaction T1 begins first, then we start transaction T2.Īfter that T1 changes row with id=10 in database and T2 reads it. “Dirty” read - one transaction can read changes of a concurrent transaction, that were not committed yet. In Spring it is possible to set one of the 5 isolation level values: DEFAULT, READ_UNCOMMITTED, READ_COMMITED, REPETABLE_READ and SERIALIZABLE.įor example, (isolation=Isolation.READ_COMMITED)Įach of these isolation levels may have or haven’t different side effects: “dirty” read, non-repeatable read and phantom read. It describes visibility of changes applied by concurrent transactions to each other. Isolation is one of the main properties of transactions (Atomicity, Consistency, Isolation, Durability). I would like to focus on two of them: Isolation and Propagation. We can use it with interfaces (lowest priority), classes or certain methods (highest is applied only for public methods and method must be invoked from outside of the annotation has multiple parameters.
spring there is annotation that can be used for wrapping a method in a transaction. Because sometimes can happen that there was an exception thrown inside your method, but transaction was not rolled back and it is not clear why? Or some other “strange” cases.
#Read committed explained how to
In general, people don’t pay attention to it while using Spring framework.īut I think, it is important to know how to use transactions properly. In my opinion transaction management is a really important topic for each backend developer.
0 kommentar(er)
