3
I always see sqls mounted with WITH NOLOCK that say improve performance, but I’ve also heard that NOLOCK is obsolete and what should be used now is READ COMMITTED SNAPSHOT, in this scenario my doubts are:
1 - NOLOCK improves even the performance or is myth, I have to measure it through a query with SET STATISTICS TIME ON for example;
2 - how do I use READ COMMITTED SNAPSHOT? some practical example that can be measured as well?
3 - What are the correspondents in Mysql and ORACLE;
2
Versions of Microsoft SQL Server prior to 2005 did not have the feature of SNAPSHOT, then the hint WITH NOLOCK could actually improve performance by making a query (a SELECT) not wait for the release of records blocked by a long transaction.
Then a brief explanation about each of the terms cited in your question:
A SELECT run in a session configured as READ COMMITTED will bring only records already committed. So if this SELECT finds a record changed by another transaction, it will be waiting for the record to be released by a COMMIT or a ROLBACK.
A SELECT using this hint will also bring records changed by another pending transaction (a transaction that has not yet completed with COMMIT or ROLBACK). Thus, this SELECT can bring "dirty" records, since you can read a value changed by that transaction but which will then be restored to its original value in the case of a ROLBACK.
It is a common feature of database servers that was only introduced in MS SQL Server in its 2005 version.
With this feature active, the DBMS keeps a photograph of the original state of the record when it is changed by a transaction. So a SELECT on another transaction can read the latest commited version of the record without waiting for the completion of the other pending transaction and without reading a "dirty record".
A SELECT run in a section configured with READ COMMITTED SNAPSHOT will read only commited records, but thanks to the SNAPSHOT feature this SELECT will not be blocked when the record has been changed by another pending transaction; instead, SELECT will read the latest commited version of the record.
It can improve yes because your Selects will never get blocked. Improves performance significantly in systems (bad) which use many long transactions. This is very difficult to measure because a locked SELECT will take more or less depending on the simultaneous use of the system by other users.
You must activate this feature SNAPSHOT in the database (the 2005 version was disabled by default - I don’t know if this has changed in the following versions) and you should configure your sessions to use this transaction isolation level (read commited snapshot). With this you can stop using the WITH NOLOCK hint without suffering with blocked Selects. It’s probably the right thing to do. Unfortunately this is also difficult to measure for the same reason already mentioned: the gain in performance depends on the simultaneous use of the system by other users.
I don’t know an equivalent to the WITH NOLOCK hint in these Dbms, but the fact is that they own the feature SNAPSHOT and it comes activated by default.
I tried to be succinct on a somewhat complex subject. If something was not clear leave a comment that I am improving the answer.
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.