Do reads from another stored procedure count for REPEATABLE READ-CodePudding

Imagine I have the following stored procedure:

CREATE PROCEDURE [dbo].[CheckIfDataIsOk]
AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    BEGIN TRANSACTION

    -- bunch of checks that read row 1

    COMMIT TRANSACTION

Edit: I put READ UNCOMMITTED in the stored procedure, but it could have been READ COMMITTED too. The point is that this first stored procedure is returning a result purely based on reading data in a table.

And I then want to use it:

CREATE PROCEDURE [dbo].[DoSomethingImportant]
AS
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRANSACTION

    EXECUTE [dbo].[CheckIfDataIsOk]

    -- if "data is ok", then do some work that modifies row1

    COMMIT TRANSACTION

Will the "repeatable read" isolation level put a read-lock when it reads data from row1 indirectly through the first stored procedure call? Or will it put the lock only when it touches row1 in the stored procedure itself?

CodePudding user response：

Don't use READ UNCOMMITED/NOLOCK. You can and will get incorrect results in the presence of concurrent transactions. And here where you

if "data is ok", then do some work that modifies row1

using READ UNCOMMITTED is simply, and clearly wrong. When reading a row that you intend to modify, you should use a restrictive lock, not just a Shared lock (or NOLOCK). When reading a row in a transaction that you intend to modify, you should read with an UPDLOCK hint.

The behavior is clearly explained in the docs:

With one exception, you can switch from one isolation level to another at any time during a transaction. The exception occurs when changing from any isolation level to SNAPSHOT isolation. Doing this causes the transaction to fail and roll back. However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.

When you change a transaction from one isolation level to another, resources that are read after the change are protected according to the rules of the new level. Resources that are read before the change continue to be protected according to the rules of the previous level. For example, if a transaction changed from READ COMMITTED to SERIALIZABLE, the shared locks acquired after the change are now held until the end of the transaction.

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

SET TRANSACTION ISOLATION LEVEL

CodePudding user response：

When executing one stored procedure from another, the execution context changes. When control returns, any changes to the execution context (such as SET options or isolation level) are rolled back to what they were before.

So when you execute

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

the isolation level is changed.

However, upon executing

EXECUTE [dbo].[CheckIfDataIsOk]

the context is saved, so that it can be rolled back afterwards.

CREATE PROCEDURE [dbo].[CheckIfDataIsOk]
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

-- bunches of checks that read row 1

COMMIT TRANSACTION

here the isolation level is changed for the duration of the procedure (note that the inner transaction does not actually commit until the outer one does). So the rows being accessed are not locked.

-- if "data is ok", then do some work that modifies row1

COMMIT TRANSACTION

At this point, the original settings are put back into place. However, previously read rows are not yet locked, and could change.

Note that REPEATABLE READ does not prevent new rows being read, it only prevents previously read rows from being changed. So it may not actually be "repeatable" in the general sense.

And to behonest, I would change the isolation level from READ UNCOMMITTED to something better. It can cause completely incorrect results, such as reading pages multiple times, reading data that simply should not be possible according to constraints, etc.