Detecting Table Locks?

[jfer]

Hi everyone, I'm creating a ASP.NET 2.0 web application utilizing sql
server 2000 as a database. My problem revolves around multiuser acces
with long running processes.

Some of the pages in the application have
long running processes against large tables in the database, lets say
that take 5minutes to complete. My problem is how do I utilize ado.net

properly in the rest of my application to display a message to users
who may try to access data associated with a table while in the midst
of one of its long running processes? For instance I would like to
notify the user that "Table X is currently locked, please try again in
a few minutes".
Do I catch sqlException and examine the .Number property? Any insight
is appreciated.


Thanks.

 

[William \(Bill\) Vaughn]

SP_Lock can provide this information. Interpreting it might be an issue...
My question would be, why are you running 5-minute processes against a table
that locks the data while doing so and still expect user interaction?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Cor Ligthert [MVP]]

jfer,

How do you lock your tables because it is certainly not a standard .Net way
of doing this.

Cor

 

[jfer]

I am actually in the process of converting a legacy fox pro application
to a web application on ASP.NET. The foxpro application currently
detects when a table is locked and instantly notifies the user via a
msgbox popup. I understand the web is supposed to be for highly
scalable / multi-user access to data. But coming from this foxpro
world I am trying to find a match. Surely there are times when a
process must exclusively lock data for a few minutes to perform a time
intensive task as a business rule. Any help on this and architecture
is greatly appreciated. Amazing how few resources on the net refer to
a topic such as this.

Thanks!

 

[Jesús López]

In SQL Server, when a connection A tries to access a resource which is
locked by other connection B. The connection A waits until connection B
finishes its works and releases the lock. By default, connection A waits
indefinitely, but you can limit that time by using SET LOCK_TIMEOUT
statement. When a connection A has set LOCK_TIMEOUT, it waits for the
specified time, if the time expires before the lock is released, then it get
a timeout error.

So, you could set LOCK_TIMEOUT to five seconds or so, and if you get a
timeout error, then you can inform the user that the operation cannot be
performed because the resources are locked by other user.

Incidentally, I would reduce as much as possible the time required to
perform the operation, I think five minutes is too much time. Can you give
us more details on the specific operation? perhaps we can help you to reduce
that time.


Regards:

Jesús López

 

[Cor Ligthert [MVP]]

Jesus,

Although what you write is very interesting and absolute not without sense
as answer, is this in my idea SQL server handling and not direct ADONET.
ADONET is based on disconnected processing were there is never something
locked. Afterward there is checked if there is something changed and the
user can be warned what to do. Around this is the dataset and the
dataadapter completely build.

If you want it in another way, you are in fact busy with going around
ADONET.

Cor

 

[Jesús López]

Sorry, but I don't understand what you mean.

Jesus,

Although what you write is very interesting and absolute not without sense
as answer, is this in my idea SQL server handling and not direct ADONET.

Yes, it is SQL Server stuff, but jfer is asking for a way to handle locks in
a large transaction. I cannot find an ADO.NET way to do that, I have to go
to SQL Server.

ADONET is based on disconnected processing were there is never something
locked.

Yes, when your are disconnected there is no locks, but when you call
DataAdapter.Update you are executing UPDATE, INSERT and DELETE statements.
These statements request an exclusive lock before procceding. Typically the
duration of these locks is very short (at most a few miliseconds), therefore
you don't have to take care of them. But in these case, a large transaction
is executing and the locks and kept for a long time. So you should take care
of this.

Afterward there is checked if there is something changed and the user can
be warned what to do.

The check is not performed afferward, the check is performed at the time you
execute DataAdapter.Update. That is what did you mean ?

Around this is the dataset and the dataadapter completely build.

If you want it in another way, you are in fact busy with going around
ADONET.

Sorry, but I don't understand. I'm form Spain and my English is not very
good.

I use ADO.NET to execute SQL statements and stored procedures. What did you
mean?


Regards:


Jesús López

 

[Cor Ligthert [MVP]]

jesuz,

Yes, when your are disconnected there is no locks, but when you call
DataAdapter.Update you are executing UPDATE, INSERT and DELETE statements.
These statements request an exclusive lock before procceding. Typically
the duration of these locks is very short (at most a few miliseconds),
therefore you don't have to take care of them. But in these case, a large
transaction is executing and the locks and kept for a long time. So you
should take care of this.

I do not see the sense of that or it should be to get the primary key back
by an autoIdent in a SQL server. But as you say so than it is probably like
that, I never tested it. A normal process of the dataadapter is done row by
row, (it takes only the changed rows to process). That can in my idea not a
reason to lock something. That locking will take more processing than the
updating of a column.

The OP is talking about 5 minutes of locking, in my idea that can never be
the situation with an update of a field in SQL server..

I am from Holland, leither is Spanish for me one of the languages I never
can understand, I don't know why because that is not with French and
Italian.

Cor

 

[jfer]

In FoxPro when attempting to utilize a table we can instantly find out
a table is locked and display a message to users. I'm aware that in
SQL the connections will sit and block while waiting to gain access to
the requested resource and that is the problem I am having. The boiler
plate code you see all over the net for wrapping database operations in
try/catch is lacking at best for a real world application, as you can
see from this problem I am raising. I saw one bit of code buried on a
forum where someone was investigating the SqlException.Number
properties to determine what happened. Setting the timeout to 5seconds
seems like a Kludge solution to me.

Please throw your hat in the circle if you have any advice.

And no its not a problem with the sql as to why the operation is taking
so long..its *ALOT OF DATA* with lots of calcuations being performed.
A damn fair use of SQL Server if you ask me (isnt that one of its
reasons for existence!?). I am very suprised if the only solution
offered to the world during this type of problem is a kludge.

Thanks

 

[Jesús López]

I have not seen what operation are you doing. As I have already said,
perhaps we can help you to reduce the time required to do the calculations
if you tell us the details. Have you considered using and creating indexed
views ?

Additionally, figuring out whether a table is locked or not is not so
difficult, you could use sp_lock stored prodedure as Bill said, or you could
just try to lock the table with a short lock timeout, and if you get a
timeout then the table is locked. But in SQL Server operations rarely
require an entire table lock, typically SQL Server locks rows, keys and
pages.


Regards:

Jesús López

 

[jfer]

Jesus the operation itself is tuned as best as it can be tuned. It
takes some time because it is running through alot of data and the task
is numerically intense.
Assume it blocks 1minute if you want. Long enough for a user who is
waiting to use the table to timeout over the HTTP protocol.

 

[Cor Ligthert [MVP]]

Bill,

From this thread I get the idea that you are advising now to use Pesimistic
Concurrency handling in AdoNet.

I never saw you do that, why this change?

Cor

 

[Jesús López]

My idea is to have the data precalculated by using indexed views, or perhaps
triggers to update some tables holding that precalculated data. The
precalculated data would always be up to date. As new data arrives to the
database the precalculated data is updated. In that way we avoid the need to
do the calculations every time a user request it.

I don't know if the precalculated data schema fits in your operation because
I don't konow the nature of the calculations. But in many cases it fits.

For example, assume you have a table with several million rows and you need
to calculate the sum of some field grouped by some other field. Instead of
doing the calculations every time a user request that information, we create
an indexed view. SQL Server stores the result of the indexed view as if it
was a real table. And getting the info would be a matter of milliseconds.

May be your calculations do not fit in this schema, but perhaps you could
divide the calculations in several phases and some of these phases fits in
this schema.

I don't understand why you have to lock tables to do some calculations and
present the results to the user.

Regards:

Jesús López



"jfer" <[email protected]> escribió en el mensaje
Jesus the operation itself is tuned as best as it can be tuned. It
takes some time because it is running through alot of data and the task
is numerically intense.
Assume it blocks 1minute if you want. Long enough for a user who is
waiting to use the table to timeout over the HTTP protocol.

 

[jfer]

Thanks for all the replies so far everyone.

I just wanted to sort of regain focus on the problem at hand... When a
user visits a page that relies on a table of data that may be
inaccessible for minutes at a time is there a way to immediately detect
that the data connection is blocked waiting to access data so that I
can output a message for the user to try the page again in a few
minutes?

Thanks.