Lock in SQL Server and Oracle

Question

Hi, I’ve been running some lock tests on SQL Server and noticed a difference from Oracle.

In oracle I did an update on a line without effecting commit or rollback and in another session I was able to query the line quietly, and of course, I visualized the previous data (consistent) and not the new value which has not yet been committed.

I did the same test in SQL Server but when performing the query in the other session it was awaited the commit or rollback of the line being updated and different from Oracle did not return me the consistent value. The SQL Server lock mechanism always works like this?

Answer 1

Yes, unlike Oracle, in SQL Server if you do not specify a block of commands within a transaction BEGIN TRAN and the operation take long, it is possible that third parties see an inconsistent situation before the end of operations.

Or insert SET IMPLICIT_TRANSACTIONS ON at the beginning of the SQL script and all operations within it will be transactions. This simulates exactly the behavior of Oracle. But don’t forget to do it COMMIT or ROLLBACK at the end, releasing the lock.