Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. Specifies that statements cannot read data that has been modified but not committed by other transactions. For more information about snapshot isolation, see Snapshot Isolation in SQL Server. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON. In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either: This is the least restrictive of the isolation levels.
This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Specifies that statements can read rows that have been modified by other transactions but not yet committed. I would definitely add my +1 to any attempt to fix this though, as unexpected locking and dirty reads feels pretty dangerous.To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation. I have no idea how complicated implementing this would be, but I assume its non-trivial as the behaviour has been known about for several years. Resetting the isolation level (as well as any other state on a connection) when a connection is returned to the pool (or alternatively when it is selected from the pool) would mean new SqlConnections are homogenous, and all behave identically, regardless of whether the connection has been used before and in what way. After all, SqlConnection instances are opened, closed, and disposed, with the state of the actual connection being completely abstracted away behind these actions. Given it is 4 years old, am I right in assuming a fix is not likely to be on the horizon? I understand that the isolation level persisting for a connection after transaction rollback or commit is likely an intended behaviour within SQL Server's implementation, but when combined with connection pooling in the SqlClient it produces behaviour that seems both unexpected and potentially dangerous.Ĭonnection pooling is a useful abstraction to avoid having to manage actual connections manually, but I think this abstraction heavily implies that separate SqlConnections will be independent, regardless of whether they use the same underlying shared connection. Thank you for your quick response - I had not spotted that other ticket.
To reproduceĬode tested with 2.1.3 (and 4.4.0), and uses Dapper (2.0.90) to execute the SQL (though Dapper is not related to the problem). This strikes me as potentially very dangerous behaviour, as unless you explicitly set the isolation level for every use of the connection, you can get essentially a random level. In the other direction, you can get dirty reads if a connection inherits a "read uncommitted" isolation level from a previous use of the connection. This can cause unexpected increases in isolation level (especially when using Serializable and Repeatable Read) that can have knock on consequences such as deadlocks. Because of this, after using a SqlConnection and setting the isolation level via a transaction, another SqlConnection created elsewhere can inherit this isolation level. However because SqlClient reuses connections in a pool, connections are not actually closed between uses of different SqlConnection instances. However, once a transaction has been committed or rolled back, the isolation level remains as it was set by the transaction, until the connection is closed or disposed. A connections default isolation level is "read commited".
0 kommentar(er)
