Does "Set Transaction Level" require begin transaction?

Question

In MS SQL Server, if I use "SET TRANSACTION ISOLATION LEVEL" in a stored procedure, do I need to wrap the select statements in a BEGIN/END TRANSACTION Block? Will the following work as expected?

CREATE PROCEDURE my_sproc AS
BEGIN

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT * FROM MyTable

END

I would expect the select statement to behave as though I had used the (NOLOCK) hint. (Which I would do if my procedure were truly this simple) — Steve Johnson, Dec 10 '10 at 15:35

score 4 · Accepted Answer · answered Dec 10 '10 at 15:17

The TRANSACTION ISOLATION LEVEL setting is a connection-level setting. There's no need to wrap it in a transaction.

That being said, you understand you will have dirty reads and such from this setting?

You can accomplish the same thing on a query-by-query basis by using locking hints such as:

SELECT * FROM MyTable WITH (NOLOCK)

score 1 · Answer 2 · answered Dec 10 '10 at 15:18

1

SET TRANSACTION ISOLATION LEVEL is different to BEGIN/COMMIT/ROLLBACK

The first changes the isolation vs concurrency settings
The second defines the atomic "unit of work"

There is no direct link or interaction: different concepts

answered Dec 10 '10 at 15:18

gbn

422,506
82
585
676

Does "Set Transaction Level" require begin transaction?

2 Answers2