I have a scenario where in I need to add a few columns to a table in one database under a SQL transaction. I also need to create some SPs in another database which refer to the column created in the table above. During the creation of the SP the transaction on the previous DB is still open. E.g. Script1 use [Test1] go begin tran tran2 go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; ALTER TABLE dbo.User1 ADD email varchar(100) NULL GO Script2 (Executed under different connection) USE [Test] GO
begin tran tran1 go create procedure [dbo].[GetUser] as begin declare @var varchar(100)
select @var = Test1.dbo.User1.email from [Test1].dbo.User1 where 1=1 print @var end
go First I run script 1 and then script 2. When the script2 runs it simply hangs. I guess since the transaction on the [Test1] database in still on. I also tried to the ‘ISOLATION LEVEL READ UNCOMMITTED’. Didn’t work. So is there a way to access columns which are created but not committed. Or any other solution.
Also one more thing, adding a column to table is a DDL change, so SQL Server will be placing a Schema modification lock on the object. And until it finishes with current operation (addition of column) you wont be able to access the table. Changing the isolation level will not have any effect on schema modification lock as its incompatible with any of the other locks. So all other transaction will be in waiting state until you close the current transaction (rollback/commit)