Locking??? - SQL Server Forums

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

Transact-SQL (2000)

Locking???

Author

Topic

vladibo
Starting Member

11 Posts

Posted - 2004-06-04 : 13:17:46

I have to execute this:

UPDATE MYTBL SET FIELD='aaa', fIELS2='bbb'
GO
pr_MyProcedure_1
GO
pr_MyProcedure_2
GO

Is there way to lock database within those 3 operations so the other user will be queued until current user is executing this and they can not interfere each other?

Is there somethink like:

LOCK

UPDATE MYTBL SET FIELD='aaa', fIELS2='bbb'
GO
pr_MyProcedure_1
GO
pr_MyProcedure_2
GO

UNLOCK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-04 : 13:21:16

Yes:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION

Your UPDATE and stored procedures calls here.

COMMIT TRANSACTION

Have a look at SET TRANSACTION ISOLATION LEVEL in BOL.

Tara

vladibo
Starting Member

11 Posts

Posted - 2004-06-04 : 13:56:14

Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 14:12:34

kinda like playing with dynamite...

quote:

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources