This means that the point-in-time view seen inside a scalar or multi-statement function invocation can be later than the point-in-time view seen by the rest of the statement. Scalar and multi-statement functions execute using a different internal T-SQL context from the containing statement. There is one last (implementation-specific) observation I want to make about RCSI before we move on. An immediate consequence of this second point is that T-SQL code running under RCSI might make decisions based on out of date information, as compared with the current committed state of the database. The second caveat is a little more subtle: RCSI provides a snapshot view of committed data as it was at the start of the statement, but there is nothing to prevent the real data from being changed (and those changes committed) while the RCSI statement is executing. For one thing, maintaining versions of committed rows consumes system resources, so it is important that the physical environment is configured to cope with this, primarily in terms of tempdb performance and memory/disk space requirements. However, these benefits do not come without costs and caveats. As a further consequence of reducing blocking due to incompatible lock requests, the opportunity for deadlocks is usually greatly reduced when running under RCSI. This advantage is commonly summarized by saying that readers do not block writers under RCSI, and vice-versa. The lack of shared locks can dramatically improve concurrency by eliminating conflicts with concurrent transactions looking to acquire incompatible locks. It is clear, for example, that a point-in-time view cannot suffer from the problems of missing rows or encountering the same row multiple times, which are both possible under locking read committed isolation.Ī second important advantage of RCSI is that it does not acquire shared locks when reading data, because the data comes from the row version store rather than being accessed directly. Seeing a point-in-time view of committed data might seem self-evidently superior to the more complex behaviour of the locking implementation. Both implementations are guaranteed to never see uncommitted data, but the data they encounter can be very different. To summarize, locking read committed sees each row as it was at the time it was briefly locked and physically read RCSI sees all rows as they were at the time the statement began. Locking read committed releases shared locks as quickly as possible, so the set of data encountered may come from very different points in time. This is quite different from the behaviour of the SQL Server locking implementation of read committed, where the statement sees the most-recently committed data as of the moment each item is physically read. The SQL Server RCSI implementation takes advantage of this to provide transactions with a point-in-time view of committed data, where that point in time is the moment the current statement began execution (not the moment any containing transaction started). There is nothing in the SQL standard that requires the data read by a read committed transaction to be the most-recently committed data. Note carefully though that this is not the same as saying that code will behave the same under RCSI as when using the locking implementation of read committed, in fact this is quite generally not the case. When this is enabled, transactions requesting read committed isolation automatically use the RCSI implementation no changes to existing T-SQL code is required to use RCSI. If the database option READ_COMMITTED_SNAPSHOT is ON, SQL Server uses a row-versioning implementation of the read committed isolation level. As it happens, both physical implementations of read committed isolation in SQL Server can experience non-repeatable reads and phantom rows, though the precise details are quite different. The standard also says that read committed transactions might experience the concurrency phenomena known as non-repeatable reads and phantoms (though they are not actually required to do so). Another way to express this requirement is to say a read committed transaction must only encounter committed data. The SQL standard requires that a transaction operating at the read committed isolation level not experience any dirty reads. While both implementations meet the requirements laid down in the SQL standard for read committed isolation behaviours, RCSI has quite different physical behaviours from the locking implementation we looked at in the previous post in this series. SQL Server provides two physical implementations of the read committed isolation level defined by the SQL standard, locking read committed and read committed snapshot isolation ( RCSI).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |