question

brutecat avatar image
brutecat asked

Is there a difference in wrapping a SELECT in a transaction with SNAPSHOT isolation?

Hi,

If I have read committed snapshot isolation enabled on a database and I have activated this as the default isolation in my session, is there a difference between wrapping a SELECT (i.e. a straight read) in a TRANSACTION block or not? Is there an overhead in doing so?

Thanks,

Stan

selecttransactionsnapshot
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Rob 5 avatar image
Rob 5 answered

Opening a transaction to issue a SELECT statement will not limit the data for each SELECT to the state of data when the BEGIN TRAN was issued, if that is what you are asking.

The transaction BEGIN TRAN, ROLLBACK and COMMIT are used for multiple INSERT/UPDATE/DELETE operations to either COMMIT (success) or ROLLBACK (not-successful).

If you are issuing a SELECT within a stored procedure to verify the data was modified correctly against some business rules, the result of the select would contain your changes in the same transaction until a COMMIT or ROLLBACK is issued.

Otherwise, I don't see a purpose to wrap just a SELECT in a transaction.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.