Apply Table Lock to SQL query

TomPreen
TomPreen used Ask the Experts™
on
I need to set a table lock in  Microsoft SQL Server 2008 for the duration of an update process.

The end user has multiple independent applications that utilise the database all from independent vendors.

I have experimented with executing a query incorporating "WITH TABLOCKX" but this results in a hang in one of the other independent applications indicating the lock is applied but not trapped.  Attached (query1.jpg) is the result of a query on sys.dm_tran_locks that I run to test for existing locks in order to determine if my process can run.
What I need is a query to apply the locks in a way that is trapped by the other independent applications.T_SQL query on sys.dm_tran_locks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Several independent applications running on one database" …  What means "Independent" here?  Different vendors?

Applications running on the same database cannot be truly independent. Such independency may cause dead locks easily.

Also, how long takes the "update process"? What exactly it does?

Author

Commented:
They are different vendors.

The process closes and updates multiple tables and creates historical data  at the end of a sales accounting period, hence the need to ensure exclusive access whilst it is running.

The time required cannot be determined and is irrelevant to the requirement.

The output for the sys.dm_tran_locks query  shown in my question shows the state of current locks set by a process running by one of the other vendors and this is exactly what I need to set in my application.
First of all the time highly matters here. If you lock the table for a long time then you may expect other applications accessing the table will report time outs etc.

Second, if you are talking about different vendors updating the same group of database tables then you cannot avoid deadlocks without their cooperation. Deadlocks frequency is dependent on many factors, e.g. the SQL Server load/speed etc. Ask these vendors how to avoid their respective applications problems during your query. To have the current status of the locks does not help much because it can be very dynamic.

Third, Why do you need Exclusive lock? Why you don't allow table reading by other applications during your queries? Are you updating tables used by other applications?

Fourth, Do you really need to lock the whole table? If you are just retrieving sales period data after its closing who can still write/change such data? If the passed sales period data can be updated then results of your query is invalidated in a short time and independently on a fact whether the table was locked or not. Did you ever observer different results with and without Exclusive table locking?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
The sys.dm_tran_locks query that produced the screen shot in my question was produced when running a specific process in another vendor application and when that process is running in  that vendor application other users of that vendor application cannot display or edit records. That is what I wish to duplicate in my application because I an updating the same tables.
So it seems the application places application locks by calling the SP sp_getapplock.  You may execute the SQL Server Profiler which will disclose exact commands used to request these locks. You may read more about the SP parameters here: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-2017

Of course, you should investigate more processes because one query result cannot tell everything.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Tom,

  Just a few few comments to add (no points please, I'd just adding a bit to what pcelba has already said).

1. On this:

What I need is a query to apply the locks in a way that is trapped by the other independent applications.

  You cannot control this; a lock is a lock whether it's at record, table, or database level.   If you lock something, take x amount of time, you may cause a timeout in another application.   If it is not written to handle that, they will get an error.

2. The closest you can come to handling this is writing it so it occurs in a single transaction, but that is certainly going to tie up the database.

3. On this:

The process closes and updates multiple tables and creates historical data  at the end of a sales accounting period, hence the need to ensure exclusive access whilst it is running.

  Consider running it in off hours while nothing else is running.

4. On this:

by calling the SP sp_getapplock.

 Applocks are different from normal locking and they only work if each process calls for a lock for the same "resource".   Unless you are in control of the other code,t this doesn't help.


 I would look closely at using off-hours scheduling as a way of achieving what you want.

Jim.

Author

Commented:
Thanks for your responses

Author

Commented:
Many thanks that would seem to be the best solution.
I cannot agree to 4.

If the Application locks are still done on the same resource then there is no reason why it should not work. Thus I've proposed to use profiler to record all these locks and investigate this possibility.

Of course, if there are gaps when other applications are not used then try it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial