Apply Table Lock to SQL query

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
TomPreenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
"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?
0
TomPreenAuthor 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.
0
pcelbaCommented:
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?
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

TomPreenAuthor 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.
0
pcelbaCommented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TomPreenAuthor Commented:
Thanks for your responses
0
TomPreenAuthor Commented:
Many thanks that would seem to be the best solution.
0
pcelbaCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.