How can I tell if a table is locked without a high level of access?

I have a problem where my WebSphere application is keeping a database table lock, or some kind of a lock, open after the user closes the browser window without logging off. I know that the DBAs can run a query against a system table to find out if the table is locked, but I don't have that access. So, I was wondering if there is another way I could find out, such as by doing some kind of a query that would hang or otherwise behave differently if the table were locked than if it were not locked. I had hoped that doing a SELECT COUNT(*) on the table would do the trick, but it worked even when I think the table is locked (but I am not sure). How about updating just one record in the table? I could try updating one record and if that fails, would that tell me if the table is locked?

As an example, here is a query I found on the web that I cannot use because I do not have access:
SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
o1.name 'object name',
o1.type_desc 'object descr',
p1.partition_id 'partition id',
p1.rows 'partition/page rows',
a1.type_desc 'index descr',
a1.container_id 'index/page container_id'
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
	ON t1.lock_owner_address = t2.resource_address
LEFT OUTER JOIN sys.objects o1 on o1.object_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id

Open in new window

jkurantAsked:
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:
First of all I have to say such tuning should do people having appropriate access rights... and such tuning should not be a part of the application itself but implemented rather as a standalone monitoring tool.

But OK, I don't know your environment...

SQL Server recognizes several different locks and you are not disclosing which one causes problems for you. You may lock the table, row, page etc. and locks can be exclusive or shared or just intentional. More about locks: https://technet.microsoft.com/en-us/library/Aa213039(v=SQL.80).aspx

Of course, to implement the theory you need some SQL command...

One possible solution is to use table hints. You may simply write:

SELECT COUNT(*) FROM SomeTable WITH (TABLOCK)
or
SELECT COUNT(*) FROM SomeTable WITH (TABLOCKX)

and trap the timeout.

More info: https://msdn.microsoft.com/en-us/library/ms187373.aspx

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
Tomas Helgi JohannssonCommented:
Hi!

An application requests during a single transaction and gets a lock on a record or table depending on how much data it is selecting,updating or deleting. This locks are released once the application issues a commit and by that tells the database that the transaction is finished.
If a session to a database is suddenly closed without a commit being issued then (if configured correctly) a rollback is done either immediately or in most cases after a certain thread idle-timeout.
If a database is not configured correctly and your application does not issue commit you can end up having a transaction locking records or tables, stopping other transactions from updating those records or tables, until a DBA kills the thread manually which is not the favorite job that a DBA does.

Regards,
    Tomas Helgi
Vadim RappCommented:
Besides, or maybe even instead, of finding out if the lock exists (and I second what pcelba said: the lock on what - single row? whole table? page? lock for reading? for updating? and so forth) - maybe even better move would be to find out why your application is leaving the lock in place, and thus eliminate the root cause of the problem.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DcpKingCommented:
I agree with Vadim, J. You should be able to trap the page-close event and issue a commit or rollback accordingly.

Mike
jkurantAuthor Commented:
Thank you for the great ideas. I was away for the weekend and am just getting back to read these possible solutions. I have not yet read the article at the link posted by pcelba, but I will read that next. As you can tell, I am trying to solve a problem we are having in production, and I hope it is okay to kind of morph this question into helping me solve this problem. So, my original question, "How can I tell if a table is locked without a high level of access?" is kind of answered at this point.

What I really need to know is: how to prevent this from happening. So, I would need to tell you about the code. Or maybe I need to close this question and start a new one. For now I will keep going. Please advise if I should start a new question.

So my code is not using transactions at all, and maybe it should. I didn't think it was necessary because I am only querying data and the user was not modifying any data when the problem occured. Here is what I think happened:

A user of my web application, called DOR, opened a table to view. The app displays the first 10, 100, etc. of records and the user can go to the next page. But I don't save anything in between. I just do a new query, skip past the records I don't need, and then output the records the user wants. So, I don't keep a recordset open.

So, a user of my web app opens a table and then closes out of the web page. Then, a delete query wants to delete some records out of the table that I had just opened to read. Apparently, the delete hangs until ... well, the DBA's killed the delete job, not mine.

So, as you can see, clues are a little hard to come by with this incident. So, all I think I can ask is: should I wrap the my SELECT in a transaction the way the DBA wants me to? He says I should do that to avoid the problem, but I am only doing a select. Do I really need to wrap it in a transaction? Will that help?

I will go read that article now. Thank you for all your help so far!
Vadim RappCommented:
> should I wrap the my SELECT in a transaction the way the DBA wants me to? He says I should do that to avoid the problem, but I am only doing a select. Do I really need to wrap it in a transaction?

Almost certainly not. Usually, when query runs, the lock is established only for the time of the query itself, it's not kept. But it can be different, there are ways to write the query so that it will indeed keep a lock.

Rather than issue recommendations based on guesswork, your DBA needs to run Profiler and find out exactly what's going on, what query is being run by your DOR, what lock is left behind, and why. Once this is found, the next step probably will be to mobify the query in DOR.
Vadim RappCommented:
...in fact, re-reading your question shows that even the fact that the problem with DELETE is because of the lock left by DOR, is just yet another guess.
pcelbaCommented:
If you have just one SELECT then you don't need any transaction. SELECT locks the table(s) depending on the transaction isolation level settings. If you need to prevent such lock(s) then you may use WITH (NOLOCK) hint(s) in the SELECT command.

The NOLOCK hint can improve the query speed a little bit but it can also allow dirty (uncommitted) data reading which I would accept in your case.

This, of course, does not explain why the table lock was not released but if you don't place any lock then it should solve the problem... To implement some user disconnection on page closing event recommended in other answers would definitely be a plus.
jkurantAuthor Commented:
Well, after reading the article on locking types I think that all I have to do is use the NOLOCK locking hint when I do my select! This is okay because the user is just looking at the data and won't care if something needed to change while they were looking. Once I test my theory I will update here.
Vadim RappCommented:
Note that even NOLOCK applies to the (usually very short) period of running the query itself - rather than to leaving anything behind when the query is complete.

And, once again, it was not proven that the problem was because of a lock to begin with, and even if it was, the lock left by your application. It could be something totally different.
jkurantAuthor Commented:
When I changed my query in DOR to add WITH(NOLOCKS), the select query no longer blocked the update query. Problem solved!
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
Databases

From novice to tech pro — start learning today.