I always see sqls mounted with WITH NOLOCK that say improve performance, but I also heard that NOLOCK is obsolete and what should be used now is READ COMMITTED SNAPSHOT, in this scenario my doubts are:
1 - NOLOCK even improves performance or is myth, I can measure this through a query with SET STATISTICS TIME ON for example;
2 - How do I use READ COMMITTED SNAPSHOT? any practical example that can be measured as well?
3 - What are the corresponding MySQL and ORACLE;
Microsoft SQL Server versions prior to 2005 did not have the SNAPSHOT feature, so hint WITH NOLOCK could actually improve performance by making a query (a SELECT) did not wait for the release of records blocked by a long transaction.
Then a brief explanation of each of the terms quoted in your question:
A SELECT executed in a session configured as READ COMMITTED will only contain committed records. So if this SELECT finds a record changed by another transaction, it will wait 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). So this SELECT can get "dirty" records, since you can read a value changed by that transaction but then 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 release.
With this active feature, the DBMS keeps a picture of the original state of the record when it is changed by a transaction. So a SELECT in another transaction can read the last committed commit of the record without having to wait for the completion of the other pending transaction and without reading a "dirty" record.
A SELECT executed in a section configured with READ COMMITTED SNAPSHOT will read only committed records, but thanks to the SNAPSHOT feature this SELECT will not be locked when the record has been changed by another pending transaction; instead, SELECT will read the last committed commit of the record.
You can improve on it because your SELECTs will never get blocked. Improves performance significantly in (bad) systems that use many long transactions. This is very difficult to measure because a blocked SELECT will take more or less depending on the concurrent use of the system by other users.
You should enable this SNAPSHOT feature in the database (in the 2005 version it was disabled by default - I do not know if that changed in the next versions) and you should configure your sessions to use this level of isolation transaction ( read commited snapshot ). With this you can stop using hint WITH NOLOCK without suffering with blocked SELECTs. This is 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 do not know an equivalent to hint WITH NOLOCK in these DBMSs, but the fact is that they have the SNAPSHOT feature and it is enabled by default.
I tried to be brief on a somewhat complex subject. If something is not clear leave a comment that I am improving the answer.
