Checking to see if Table is being processed

Question

Checking to see if Table is being processed

Chaz-265757

Old Hand

Points: 333
More actions
January 30, 2006 at 4:04 pm

#103065

Hi to all

I do not know if this was asked before, but here it is.

Is there anyway in T-SQL to tell if a Table is being process, (insert or update is being performed)?

I need to know before I process a select on that table.

This can be a SP also

Thanks
Kindest Regards,
Chaz

Viewing 13 posts - 1 through 12 (of 12 total)

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

The most efficient way that I can think of is to use xp_sendmail at the end of your Inserts & Updates to send an email to you when an Insert and or Update has taken place.

You could use triggers but on a heavliy used OLTP Database I probably wouldn't do it like that!

Kindest Regards,

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 2

Ummmm.... you could us sp_lock to see if the table is being worked on by the type of lock it has. You would, of course, need to know the ID of the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JeffB Hall of Fame Points: 3541 More actions · Answer 3

If you are concerned about getting back dirty data or some incomplete data, SQL Server normally prevents this. Your select statement will wait for any inserts or updates that are in process to complete.

vadba SSChampion Points: 11133 More actions · Answer 4

You may also want to read about isolation levels. Search BOL for SET TRANSACTION ISOLATION LEVEL

Chaz-265757 Old Hand Points: 333 More actions · Answer 5

The reason I want to know, when people come to my site and the tables are in process mode I will send a message to come back later.

Thanks

Kindest Regards,

Chaz

JeffB Hall of Fame Points: 3541 More actions · Answer 6

I'm not clear what you mean by 'in process mode'. If you mean the tables are being accessed via INSERT/UPDATE statement, then in a typical OLTP system, they will be always 'in process mode' in a moderately used system. If you mean that some sort of ETL, nightly job or bulk load is running then you could write an indicator to a table and clear it when done, i.e.:

update inprocess set inprocessflag = 1

..... do the long running process

update inprocess set inprocessflag = 0

Chaz-265757 Old Hand Points: 333 More actions · Answer 7

Yes, tables are being accessed via insert/update

or a SQL command that look for the transaction isolation level on the table

Kindest Regards,

Chaz

JeffB Hall of Fame Points: 3541 More actions · Answer 8

If someone is inserting or updating data into the system, why do you want to send your users away? There query may take a little longer because of waiting for the update to complete but they will get the correct answer. The length of an insert/update transaction should be optimized to have a sub-second response time.

Chaz-265757 Old Hand Points: 333 More actions · Answer 9

He have processes that load data into tables every 15 min and another process every 30 min. and web user viewing data around the same time, this can cause a deadlock because the processes are updating the same tables and user are trying to view data. Our web server is going down due to the waiting time; we are trying to find the best solution to resolve this issue.

Kindest Regards,

Chaz

JeffB Hall of Fame Points: 3541 More actions · Answer 10

I would optimize the insert/updates to not block the reads. You could shorten or eliminate the use of transactions or allow the reads to not wait with the WITH NOLOCK hint on the user's query. If I were a user and as told to come back later every 15 minutes, I'd go away and never come back.

However, if you want turn away users whenever processing is going on, you could use my suggestion of creating a table and monitoring its status

On the SQL Server:

update inprocess set inprocessflag = 1

..... do the every 15 minute process

update inprocess set inprocessflag = 0

On the web server:

select inprocessflag from inprocess

if inprocessflag = 1 then

write "System is processing, try back later"

else

execute queries and do normal processing

end if

Chaz-265757 Old Hand Points: 333 More actions · Answer 11

Thanks a lot, I'll try the optimize first.

Kindest Regards,

Chaz

Scott Beckstead Right there with Babe Points: 780 More actions · Answer 12

Sounds like you could use another table with lock columns that are set and cleared by insert/update triggers and post insert/update triggers, beyond that you could monitor the lock status of the tables in question and only allow selects when these are clear. What you really want though is another database for queries that replicates the database being updated. However being as resource bound as you are have you considered a new server that is fast enough to handle your data/requests?

scott

Kindest Regards,

Scott Beckstead

"We cannot defend freedom abroad by abandoning it here at home!"
Edward R. Murrow

scottbeckstead.com