creating global variable as a flag

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 2000 Forums

SQL Server Development (2000)

creating global variable as a flag

Author

Topic

rex99
Starting Member

5 Posts

Posted - 2004-07-26 : 07:04:30

Hey all,

I have a number of stored procedures to run multiple times as a
single transaction. To avoid letting another user run the same
transaction at the same time, I have heard that it is possible
to declare a global variable which will sit on the database. I
could set this varable to a certain value when I begin and then
end the transaction. Not much info online about this, so I'm
wondering if it's true at all.

Has anyone used varables like this before?

cheers

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 08:56:04

Don't know about a global variable, but you could create a global temporary table e.g.

CREATE TABLE ##JobIsLocked ( LockedBy varchar(10) )

and then test for the existence of this table as the semaphore for your lock.

Beware that if the process terminates with an error this table wil NOT be deleted, and thus no other process will be able to start until the table is deleted manually, or SQL Server restarts.

You could probably also wrap your routines in a BEGIN TRANSACTION / COMMIT block, and the store some value, initially, which would create a DEADLOCK for any subsequent duplicate attempt. That would have the benefit of automatically being released by SQL Server if the process failed etc.

Kristen

rex99
Starting Member

5 Posts

Posted - 2004-07-26 : 09:16:36

Thanks Kristen

What's happening is that I've got a number of VB.NET functons
which call a number of stored procedures. While transaction-management
capabilities can be found in .NET and in SQL Server, I have my
doubts if I can wrap the whole thing in one transaction.
Would be nice though..

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 09:39:52

Can you let us know why you want to do this?

Overall, I'd say let sql server manage your transactions...

Sounds like your trying to build a bottle neck...and then basically everything would then crawl to a halt...

Unless, I'm misunderstnading...(WHAT? AGAIN? Damn I hate when that happens)

Brett

8-)

rex99
Starting Member

5 Posts

Posted - 2004-07-26 : 09:56:34

Well...

I want to insert a couple of thousand records at a time. I need to
run looped queries to find exactly what will go into some of these
records. The result is that this operation can take well over 10
seconds. I had a problem where someone ran the query and then someone
else ran it while the first job was executing.
Net result was that a large number of duplicate records were
inserted.

Granted, this might never happen again, but I still want to
safeguard against it in my code.

Make sense?

Cheers..

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 10:05:14

Guess we don't want to have a discussion about constraints....

So...how's the weather?

I digress....

When you say "queries", I'm assuming a pretty loose security environment...

Are these sprocs? Or is this all done from asp, .net, ect, or QA?

Anyway....what I did was to create a "system variable" table with the aid of a trigger to ensure that there is only 1 row...and all my vars are stored there...

That precludes that everyone use that table when I need to do my quarterly load processes...if you can't ensure that, then it's open season....

Or

It's wabbit season...


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[System_Var_UpdTr]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[System_Var_UpdTr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[System_Var]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[System_Var]
GO

CREATE TABLE [dbo].[System_Var] (
	[Proof_Year] [char] (4) NOT NULL ,
	[Proof_Quarter] [char] (2) NOT NULL ,
	[Proof_Month] [char] (2) NOT NULL ,
	[ATS_Ind] [int] NOT NULL ,
	[Updated_By] [char] (8) NOT NULL ,
	[Updated_Ts] [datetime] NOT NULL ,
	[System_State] [char] (2) NULL ,
	[Load_Ind] [char] (1) NOT NULL ,
	[Load_Message] [varchar] (255) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[System_Var] WITH NOCHECK ADD 
	CONSTRAINT [DF__System_Va__ATS_I__7B5B524B] DEFAULT (0) FOR [ATS_Ind],
	CONSTRAINT [DF__System_Va__Updat__7C4F7684] DEFAULT (getdate()) FOR [Updated_Ts],
	CONSTRAINT [DF__System_VA__Load___30CE2BBB] DEFAULT ('N') FOR [Load_Ind]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE TRIGGER System_Var_UpdTr ON System_Var
FOR INSERT, UPDATE, DELETE 
AS

If Exists (Select 1 From Inserted) And Not Exists (Select 1 From Deleted)
	BEGIN    
		If (Select Count(*) From System_Var) > 1
			BEGIN
				ROLLBACK
				RAISERROR 50001  'The System Variable Table may only contain 1 row'
			END 
		Insert Into System_Var_H (
		       HIST_ADD_TYPE 
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Proof_Year
		      ,Proof_Quarter
		      ,Proof_Month
		      ,ATS_Ind
		      ,System_State
		      ,Load_Ind
		      ,Load_Message
		      ,Updated_By 
		      ,Updated_Ts
		)
		Select
		      'I'
		      ,Updated_By 
		      ,user
		      ,Proof_Year
		      ,Proof_Quarter
		      ,Proof_Month
		      ,ATS_Ind
		      ,System_State
		      ,Load_Ind
		      ,Load_Message
		      ,Updated_By 
		      ,Updated_Ts
		From   Inserted	
	END

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
	BEGIN    
		Insert Into System_Var_H (
		       HIST_ADD_TYPE 
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Proof_Year
		      ,Proof_Quarter
		      ,Proof_Month
		      ,ATS_Ind
		      ,System_State
		      ,Load_Ind
		      ,Load_Message
		      ,Updated_By 
		      ,Updated_Ts
		)
		Select
		       'U'
		      ,(Select Inserted.Updated_By from Inserted) 
			  ,user
			  ,Proof_Year
			  ,Proof_Quarter
			  ,Proof_Month
			  ,ATS_Ind
		          ,System_State
		          ,Load_Ind
		      	  ,Load_Message
			  ,Updated_By 
		          ,Updated_Ts
		From  Deleted	 
	END


If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

	BEGIN     
		Insert Into System_Var_H (
		       HIST_ADD_TYPE 
		      ,HIST_ADD_BY
		      ,HIST_ADD_SYSUSER_BY
		      ,Proof_Year
		      ,Proof_Quarter
		      ,Proof_Month
		      ,ATS_Ind
		      ,System_State
		      ,Load_Ind
		      ,Load_Message
		      ,Updated_By 
		      ,Updated_Ts
		)
		Select
		          'D'
		          ,user
			  ,user
			  ,Proof_Year
			  ,Proof_Quarter
			  ,Proof_Month
			  ,ATS_Ind
		      	  ,System_State
		      	  ,Load_Ind
		      	  ,Load_Message
			  ,Updated_By 
		      	  ,Updated_Ts
		From  Deleted	
	END
  



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-26 : 10:47:06

What you need are Locking Hints or Transcation Isolation Levels with your SELECT statements nicely wrapped inside a transaction. So you can basically tell SQL Server that you want this table locked while you read it and then do some stuff with this data. These concepts are explained quite well in Books Online, take some time to read them.

But also listen to what the others are trying to tell you, if you attempt to lock data yourself, you might end up in a lot of trouble. SQL Server was designed for concurrent access, so any attempt to control concurrency will have implications on the scalability of your application, and worse, expose users to deadlocks. Make sure you do your reading before you decide which way you want to go with this.

OS

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-26 : 11:44:23

See SET CONTEXT_INFO in BOL for the closest thing to a global variable (without temp tables)

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources