Lockings HInts - Help Required

Question

Lockings HInts - Help Required

ColdCoffee

SSC-Dedicated

Points: 39972
More actions
January 6, 2010 at 11:36 pm

#214320

Hi All,
Issue Description:
-----------------
Process 1 - updates (or rather truncates and populates) a set of 10 tables through an EXE.
Process 2 - Reads and populates temp tables with data from one or more tables during exactly the same time when Process 1 is processing.
Now as process 1 has no data in tables while it truncates, my Process 2 is populating temp tables with 0 records.
Requirement:
-------------
I don't have any hold on Process 1 nor I can introduce some locks durinng Process 1. I can handle anything in Process 2 'coz it's my own T-SQL procedure. Now i need a mechanism to let my Process 2 know that Process 1 is processing a table and hence wait for the Process 1 to complete fully and start Process 2!
Is that possible? I am reading about the Lock Hints, but my brain size is of humming bird's :ermm: so cant stuff it too much.
Please help me out with this!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Locking hints would have to be applied in Process1, since it's the one doing the updating.

May I suggest a check at the beginning of Process2, something to check whether Process1 has finished or not. Put in a loop with a Waitfor delay, then process2 can wait until 1 has completed.

How, well that depends on what signals that Process1 has finished.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

May be a check on the row count of the last table being updated by process 1. Since truncation will return a count = 0 and after that count will be > 0. The issue (if at all)is that if process 2 runs before process 1 it will return count > 0.

"Keep Trying"

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

THanks for the replies guyz.

I already thought of doing the checking the table count, as chiragNS said, but the catch is Process 1 deals with 10 tables and PRocess 2 depends on all 10 tables; hence checking all 10 tables' counts will become cumbersome.

What i am probabaly thinking of doing is to Lock the whole DB before the start of Process 1 and releasing the lock after it completes. Having said that, i have a question arising at this approach.

Question :

SP 1 - Executes process 1 and process 2

SP 2 - Executes process 3 (dealing with the same tables)

Now, if Process 1 fails during it's execution (remember i have locked the whole DB), will Process 3 suffer from the locks that were acquired during Process 1?

Also can u gurus give me some piece of code where i can lock the entire DB before a process starts and release the lock after process ends?

Something like

--- Put lock on DB

-- Execute the PRocess

--- Release lock on DB

Is that possible?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I strongly suggest you don't go the locking route. If you could lock the entire database, you'd essentially be preventing anyone from doing anything until the first process is complete. Is it acceptable to essentially turn the database single-user for the duration of that process?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Also, you said Process1 is an executable. Does it open its own connections to the database server? If so, any locks acquired will have to be acquired by the executable not a process that calls the executable, as locks are per connection

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 6

Maybe you could add a simple monitoring table (three columns: procedure, status, changetime).

When you start proc1, change the status to 1, at the end of proc1 change it back to 0. Then use the while loop inside proc2 as Gail suggested to check if proc1 is still running. Just a basic thought of concept.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

@Imu92, thats what i suggested as a fix, but as it is involving a lot of changes in a lot of changes, we are reluctanct on that!

@Gail, as u said, process 1 cant throw signals if a tables is getting processed! Am analyzing on all fronts.

Between, can we know if a table if being used by some process (can be T-SQL code or SP or some user executing a select query ect etc) through T-SQL queries?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Only if it's holding locks. That's the SQL definition of 'using a table'. Check sys.dm_tran_locks. Also, if you know what application name it's set (if it has set one) you can query the connection-related tables to see if it's still connected. sys.dm_exec_sessions and sys.dm_exec_requests.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 9

COldCoffee (1/7/2010)
What I am probabaly thinking of doing is to Lock the whole DB before the start of Process 1 and releasing the lock after it completes.

Yes - this would be an application lock, probably held at the session level. Whenever I come across a requirement that seems to require a DB-level 'lock', it often signals that an application lock is required.

Sample code to acquire a lock:

DECLARE @result INTEGER;

EXEC @result =

sp_getapplock

@Resource = N'Insert good name here',

@LockOwner = N'Session',

@LockMode = N'Exclusive';

SELECT @result;

That's for process 1. Process 2 would probably acquire a Shared application lock - which would block process 1, but not other processes requiring a Shared lock.

Be sure to call sp_releaseapplock the same number of times as sp_getapplock, handle return codes correctly (important!), and also see the documentation for APPLOCK_TEST and APPLOCK_MODE.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi