SQL Server "Checking Database Table If there is a process going on the table"

Question

Hello all

What I want to do is, inform the users about my program that "there is an update going on in database, so the results may not be correct." Thus I have to check if there is some process (like writing or deleting) on my table which my program uses.

Edit: The way that I update my table is I use MS Access, I copy from MS Excell about 10.000 rows and paste it to the database using MS Access. So when the copy paste is in progress, I want to be able see it from my web based program.

Thanks.

Answer 1

Make a shared counter somewhere (in the process, or if on a server, in a file or shared memory, etc.). Do your updates like this

increment the counter
query(UPDATE blablabla)
decrement the counter

Then you show the message if the counter is larger than 0.

(But is this what you really want? See comment)

Answer 2

I'm not certain I understand what you are trying to achieve here.

SQL Server manages the integrity and consistency of your data for you, provided you are using a suitable Isolation Level.

So, if your count/reporting query attempts to access a record that is currently being updated, it will not be able to acquire a shared lock (on a record currently being updated) until after the update operation has completed, thereby ensuring only committed data is reported. The report is therefore correct as of the time it completes.

For further reading see: Transaction Isolation Levels

Answer 3

Update, re:

"Edit: The way that I update my table is I use MS Access, I copy from MS Excell about 10.000 rows and paste it to the database using MS Access. So when the copy paste is in progress, I want to be able see it from my web based program."

The principle is the same, at the start of your paste process, add a DB row, like in my first answer. Or, you could set a global variable in your web application, something like updateInProgress = true.

But, how long does this copy take? If it's less than about 5 minutes, then it's probably not worth doing anything else.

If you just want to let the user know that there is "fresh" data, you could query for recent datetimes.

---

What kind of update? In the usual sense, SQL Server keeps the results accurate with default transaction locking.

Maybe you mean a long data-import job? Or, Heaven forbid, some intensive, cursor-driven process?

Then you could do something like:

1. Create a table in the database. Call it, say LongJobLog. It might have 3 columns: an ID, a start datetime, and an end datetime.

2. At the beginning of each long job, add a new start time to this table, be sure to save the new ID to a variable.

3. When the job ends, or errors out, update the ID's row with the endtime.

4. Your application would query this table for recent rows (by the start time) that still had a null end time. If such rows existed, then an "update" is in progress.

5. You'll probably need a cron job to clear out entries where jobs crashed.

Alternatively, this start-stop information is probably available in the SQL logs. It might take a bit of work to sort it out, though.