Locking everyone/everything out while updating? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Locking everyone/everything out while updating?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-06-27 : 17:07:05

In MS SQL 2005

With these tables: GrandPrntTable-->ChildTable-->GrandChldTable

Here's what I need to do.

1: Lock out any other changes or table access for the three tables while the following happens
2: ALTER TABLE GrandPrntTable, ChildTable, GrandChldTable NOCHECK CONSTRAINT ALL
3: Perform some UPDATES and row DELETEs
4: ALTER TABLE GrandPrntTable, ChildTable, GrandChldTable CHECK CONSTRAINT ALL
5: Release the locks (if necessary)

Is it possible to do this? Lock out all three tables, while I make changes.

Can anyone offer an example on how this would be done?

Thanks!

--PhB

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 18:22:34

you can use TABLOCK hint.

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-06-27 : 18:29:55

OK, Thanks for your reply. How is that done with three tables at the same time? I just want to know what the syntax is for locking the three tables.

--PhB

phrankbooth
Posting Yak Master

162 Posts

Posted - 2008-06-28 : 10:57:24

Let me clarify a bit here:

-Since I'm disabling constraints I assume I need to lock out all transactions/processes etc... so that I can safely do the changes I need to make without disturbing incoming tranactions...

-This is what I'm thinking of doing below. Is this correct, will it achieve the stated results?
ALTER TABLE ParentTbl, ChildTbl, GrndChldTbl NOCHECK CONSTRAINT ALL
UPDATE
(SELECT *
FROM ParentTbl WITH (TABLOCKX) INNER JOIN
ChildTbl WITH (TABLOCKX) ON ParentTbl .ID = ChildTbl .ID INNER JOIN
GrndChldTbl WITH (TABLOCKX) ON ChildTbl.ID = GrndChldTbl .TestID) as TblSet
Set TblSet.ParentTbl.ID=9999, TblSet.ChildTbl.ID=8888, ParentTbl.GrndChldTbl.ID=7777
Where TblSet.ParentTbl.ID=0000
ALTER TABLE ParentTbl, ChildTbl, GrndChldTbl CHECK CONSTRAINT ALL

--PhB

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-28 : 14:52:39

Where is SET after update?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-28 : 22:48:05

Set TblSet.ParentTbl.ID=9999, TblSet.ChildTbl.ID=8888, ParentTbl.GrndChldTbl.ID=7777

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources