Lock table access

[Glasgow]

I have a stored procedure that deletes the contents of 3 tables then re-populates those tables from an external source.

What I want to avoid is two users running the same procedure concurrently - i.e. where one user may be in the process of re-populating the tables while another (who started the same procedure a fraction of a second later) is trying to delete the content of those same tables.

Would BEGIN TRANSACTION /COMMIT TRANSACTION be enough or do I need to know/do more?

 

[pjw001]

I assume that the results of whatever is being done to these tables will be returned to the user at the end of the procedure and never be needed again.

You could try doing an update of all three tables (without a where clause) at the start of the transaction.

 

[JackVam]

If you use TABLOCKX - this will place an exclusive lock on the table. Keep in mind , this will disallow anyone else to read or update

Jack Vamvas

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[Glasgow]

Thanks for replies.

pjw001 - assumption is correct. Not sure why you are suggesting 'without a WHERE clause' but these tables are loaded using a number of fairly complex queries.

JackVam - The TABLOCKX option could be useful, thanks but presumably this will release the first table as soon as processing moves to the second one? Ideally I'd like to lock out all 3 tables - i.e. restrict running of the store procedure to a single user.

I guess what I could do is simply trap for the existence of the first table before allowing the procedure to be run. The only problem with that is the fact that the table might exist for the 'wrong reasons' - e.g. the procedure failed on its last execution and therefore did not complete its housekeeping.

 

[pjw001]

The purpose of doing the update without a where clause is to ensure that all rows are locked.

Using TABLOCKXX would probably also work and the lock would not be released until the end of the transaction.

Another option might be to use temporary tables, although you would have to include the create code.

 

[Glasgow]

Thanks again. Temporary tables was where I started from (subject of a separate thread) but the fact that they are destroyed on the completion of the stored procedure is a bit of a problem for me as I need to query them immediately afterwards.

 

[pjw001]

I said "I assume that the results of whatever is being done to these tables will be returned to the user at the end of the procedure and never be needed again."

You said that the above assumption was correct. Now you say the results are required after the procedure has completed.

You will need to lock the tables in such a way that the locks are held until after the data has been queried.

You might get away with what I have suggested most of the time, but sooner or later the tables will be attacked by another user before the first user gets his results.

 

[Glasgow]

Apologies for the confusion but it's not strictly a complete contradiction. The tables are populated by the stored procedure, queried immediately after the procedure call then they are no longer needed.

I think I will set up a separate table specifically for the purpose of locking tasks. I will interrogate that table before starting the task. If there is no entry to suggest that the task is in progress, I will create one and at the end of the task I will remove it. Yes there is a danger that failures could cause such table entries to be left hanging around but I think I can code workarounds for that.

Thanks again.

 

[danvlas]

I guess RiverGuy gave you a useful piece of info in your other thread (identify the session).

Why not use it in this case too?

This way you don't need to delete anything.

Just add a varchar column to all tables, get the current date and time (getdate()), concatenate it with the @@spid, do your insert to all tables and populate the column with the concatenation, then return only the records that match that concatenated value.

This way, every user will have its own set of data and you can also keep the record history...

Just an opinion...



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Glasgow]

I have a lot of insert and select statements that I'd have to change and the separate table approach gives me a simple general process locking facility that I can apply to any process I want so I think I will stick with it despite its limitations and see how it pans out in practice.

Thanks for the input though, it's appreciated! The 'session in a column' idea is a good one which I could well use in future.