Post reply

Checking to see if Table is being processed

  • 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

  • January 30, 2006 at 4:20 pm

    #617849

    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,

  • January 30, 2006 at 8:08 pm

    #617868

    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)
    Intro to Tally Tables and Functions

  • January 31, 2006 at 7:51 am

    #617931

    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.

     

  • January 31, 2006 at 8:36 am

    #617950

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

     

  • January 31, 2006 at 10:02 am

    #617965

    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

  • January 31, 2006 at 1:46 pm

    #618005

    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

     

  • January 31, 2006 at 2:26 pm

    #618009

    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

  • January 31, 2006 at 2:42 pm

    #618011

    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.

  • January 31, 2006 at 3:09 pm

    #618015

    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

  • January 31, 2006 at 3:32 pm

    #618019

    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

     

  • January 31, 2006 at 3:48 pm

    #618023

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

    Kindest Regards,

    Chaz

  • February 1, 2006 at 11:43 am

    #618187

    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

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

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