SQL Nested transaction

Question

Need explanation for the below query. While committing the outer transaction T , the row has been already deleted by nested transaction. But still the value with ID 2 is selected and displayed.

BEGIN TRAN T
SELECT * from tbl_types where ID=2

    BEGIN TRAN nested
       DELETE from tbl_types where ID=2
    COMMIT TRAN nested

COMMIT TRAN T

See [execution plan](http://sqlfiddle.com/#!6/79e37/9/0), this is normal sequential order — Lukasz Szozda, Sep 12 '15 at 08:18
This is about "dirty read". Consider read http://stackoverflow.com/questions/20840/sql-server-dirty-reads-pros-cons — Burcin, Sep 12 '15 at 11:02

user2864740 · Accepted Answer · 2015-09-12T08:18:11.337

The result set from the first select has already been sent back to the client.

It doesn't matter what has happened since; the next query will find no matches for the since-deleted records.

This is simply related to statement ordering. The following, without any nested/explicit transactions, would produce the same results:

SELECT * from tbl_types where ID=2
DELETE from tbl_types where ID=2

While this would return no results:

DELETE from tbl_types where ID=2
SELECT * from tbl_types where ID=2

SQL Nested transaction

1 Answers1