Here is an simple experiment that can provide some proof.
CREATE PROC InnerSP AS BEGIN BEGIN TRAN UPDATE dbo.Tab1 SET Name='Name1' WHERE Id=1 SELECT 'After Update Tab1' SELECT * FROM sys.dm_tran_locks WHERE resource_type != 'DATABASE' UPDATE dbo.Tab2 SET City='Seattle' WHERE Id=1 COMMIT TRAN SELECT '(InnerSP) After Commit' SELECT * FROM sys.dm_tran_locks WHERE resource_type != 'DATABASE' END GO CREATE PROC OuterSP AS BEGIN BEGIN TRAN EXEC InnerSP -- cut -- COMMIT TRAN SELECT '(OuterSP) After Commit' SELECT * FROM sys.dm_tran_locks WHERE resource_type != 'DATABASE' END GO EXEC OuterSP
OuterSP calls InnerSP and InnerSP has BEGIN TRAN - COMMIT TRAN. If the exclusive locks are released after innerSP commit, dm_tran_locks will show no locks are held against Tab1 and Tab2 tables. But as shown below, the result is that the X locks are still intact after inner SP commit. Their locks are only released when outer SP commit is called.
The example above has UPDATE statements whose lock type is X (exclusive) lock. For Insert, Update, Delete, it holds X lock within a transaction, but for SELECT - within a transaction - the Shared (S) lock will be released once the rows are read in the default READ COMMITTED isolation level. If transaction isolation level is higher, S lock will last until it meets COMMIT.
On another note, if ROLLBACK is called in nested SP, the entire transaction is rolled back regardless of nested SP level. For example, when you have a SP that calls nestedSP1 that calls nestedSP2, if you call ROLLBACK in nestedSP2, the whole transaction from outmost SP will be rolled back. To avoid this, you can limit the transaction scope by using save transaction point. An example shown below rolls back nestedSP transaction only and UPDATE in outerSP is still valid.
CREATE PROC NestedSP AS BEGIN BEGIN TRY SAVE TRAN T2 UPDATE dbo.Tab SET City='Redmond' WHERE Id=1 RAISERROR ('Error', 16, 1 ); COMMIT TRAN T2 END TRY BEGIN CATCH ROLLBACK TRAN T2 END CATCH END GO CREATE PROC OuterSP AS BEGIN BEGIN TRAN T1 EXEC NestedSP -- cut -- UPDATE dbo.Tab SET City='Seattle' WHERE Id=1 COMMIT TRAN T1 END GO EXEC OuterSP