The “phantom read” in MySQL on RR isolation level is hidden deep, but still can reproduce it. Here are the steps: create table ab(a int primary key, b int); Tx1: begin; select * from ab; // empty set Tx2: begin; insert into ab values(1,1); commit; Tx1: select * from ab; // empty set, expected phantom … Read more
From Wikipedia (which has great and detailed examples for this): A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. and A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection … Read more
From official doc – 4.3.1 The default isolation level for that method is SERIALIZABLE, which is quite aggressive: READ_COMMITTED would work just as well; READ_UNCOMMITTED would be fine if two processes are not likely to collide in this way. However, since a call to the create* method is quite short, it is unlikely that the … Read more
The EF Core code is exactly the same. DbContext.Database.BeginTransaction(IsolationLevel.Snapshot); The only difference is that in EF Core the method with isolation level (as many others) is an extension method, defined in RelationalDatabaseFacadeExtensions class, and importantly, located in Microsoft.EntityFrameworkCore.Relational assembly. So if you have using Microsoft.EntityFrameworkCore; and don’t see it, add reference to the Microsoft.EntityFrameworkCore.Relational.dll assembly … Read more
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SESSION is optional, just limits the setting to the current session. READ UNCOMMITTED is to be replaced with your desired level. https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html
Run the following and see for yourself: CREATE PROCEDURE dbo.KeepsIsolation AS BEGIN PRINT ‘Inside sproc that does not change isolation level’; DBCC USEROPTIONS; END GO CREATE PROCEDURE dbo.ChangesIsolation AS BEGIN PRINT ‘Inside sproc that changes isolation level’; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; DBCC USEROPTIONS; END GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; DBCC USEROPTIONS; … Read more
Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, ‘dirty’ read. It makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it … Read more
NOLOCK : Is equivalent to READ UNCOMMITTED (source : MSDN) NOLOCK or READ UNCOMMITTED Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. … Read more
READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes. Microsoft recommends READ COMMITTED SNAPSHOT for most apps that need row versioning. Read this excellent Microsoft article: Choosing Row Versioning-based Isolation Levels. It explains the benefits and costs of both isolation levels. And here’s a more thorough … Read more
The fact Serializable is the default comes from times when .NET wasn’t even released (before year 1999), from DTC (Distributed Transaction Coordinator) programming. DTC uses a native ISOLATIONLEVEL enumeration: ISOLATIONLEVEL_SERIALIZABLE Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. No new data can be inserted that would … Read more