Accessing column created within a transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Accessing column created within a transaction

Author

Topic

kairan_r
Starting Member

1 Post

Posted - 2013-06-25 : 01:39:38

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 01:41:42

can i ask the need for this requirement? why creating column inside a transaction? why cant column be created outside and then data being added/modified inside transaction?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 01:45:33

Also the way you've written transaction you're ending batch immediately after it. Is this intentional?

begin tran tran2
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 01:58:22

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)

More details here

http://aboutsqlserver.com/2012/04/05/locking-in-microsoft-sql-server-part-13-schema-locks/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources