Imagine I have the following stored proc:
CREATE PROCEDURE [dbo].[CheckIfDataIsOk]
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
-- bunches of checks that read row 1
COMMIT TRANSACTION
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 proc call ? Or will it put the lock only when it touches row1 in the stored proc 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.