Detecting Table Locks? -- dotnet ado.net

jfer
12/26/2006 9:37:39 PM

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!

[quoted text, click to view]

Jesús López
12/27/2006 9:30:14 AM

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

"jfer" <nicemonitor@hotmail.com> escribió en el mensaje
news:1167197859.477621.112880@79g2000cws.googlegroups.com...
[quoted text, click to view]

jfer
12/27/2006 11:25:59 AM

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

[quoted text, click to view]

Cor Ligthert [MVP]
12/27/2006 12:09:42 PM

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" <sqlranger.mvp@mvps.org> schreef in bericht
news:%23uDX%23EZKHHA.5104@TK2MSFTNGP06.phx.gbl...
[quoted text, click to view]

Jesús López
12/27/2006 1:15:20 PM

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

"Cor Ligthert [MVP]" <notmyfirstname@planet.nl> escribió en el mensaje
news:u0eA0caKHHA.4992@TK2MSFTNGP04.phx.gbl...
[quoted text, click to view]

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.

[quoted text, click to view]

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.

[quoted text, click to view]

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

[quoted text, click to view]

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

[quoted text, click to view]

Cor Ligthert [MVP]
12/27/2006 1:42:20 PM

jesuz,

[quoted text, click to view]
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
12/27/2006 6:19:52 PM

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.

[quoted text, click to view]

Jesús López
12/28/2006 2:16:18 AM

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

Cor Ligthert [MVP]
12/28/2006 4:08:11 AM

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

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> schreef in bericht
news:%23gstJ77JHHA.4712@TK2MSFTNGP04.phx.gbl...
[quoted text, click to view]

Jesús López
12/28/2006 1:38:11 PM

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" <nicemonitor@hotmail.com> escribió en el mensaje
news:1167272392.837174.15390@73g2000cwn.googlegroups.com...
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.

[quoted text, click to view]

jfer
1/8/2007 10:09:17 PM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : Detecting Table Locks?