Detecting Table Locks?

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.

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)
-----------------------------------------------------------------------------------------------------------------------

"jfer" <nicemonitor@hotmail.com> wrote in message
news:1166995538.087683.312160@79g2000cws.googlegroups.com...
> 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.
>

jfer,

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

Cor

"jfer" <nicemonitor@hotmail.com> schreef in bericht
news:1166995538.087683.312160@79g2000cws.googlegroups.com...
> 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.
>

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!


Cor Ligthert [MVP] wrote:
> jfer,
>
> How do you lock your tables because it is certainly not a standard .Net way
> of doing this.
>
> Cor
>
> "jfer" <nicemonitor@hotmail.com> schreef in bericht
> news:1166995538.087683.312160@79g2000cws.googlegroups.com...
> > 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.
> >

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...
>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!
>
>
> Cor Ligthert [MVP] wrote:
>> jfer,
>>
>> How do you lock your tables because it is certainly not a standard .Net
>> way
>> of doing this.
>>
>> Cor
>>
>> "jfer" <nicemonitor@hotmail.com> schreef in bericht
>> news:1166995538.087683.312160@79g2000cws.googlegroups.com...
>> > 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.
>> >
>

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...
> 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...
>>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!
>>
>>
>> Cor Ligthert [MVP] wrote:
>>> jfer,
>>>
>>> How do you lock your tables because it is certainly not a standard .Net
>>> way
>>> of doing this.
>>>
>>> Cor
>>>
>>> "jfer" <nicemonitor@hotmail.com> schreef in bericht
>>> news:1166995538.087683.312160@79g2000cws.googlegroups.com...
>>> > 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.
>>> >
>>
>
>

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...
> 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
>
> "Jesús López" <sqlranger.mvp@mvps.org> schreef in bericht
> news:%23uDX%23EZKHHA.5104@TK2MSFTNGP06.phx.gbl...
>> 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...
>>>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!
>>>
>>>
>>> Cor Ligthert [MVP] wrote:
>>>> jfer,
>>>>
>>>> How do you lock your tables because it is certainly not a standard .Net
>>>> way
>>>> of doing this.
>>>>
>>>> Cor
>>>>
>>>> "jfer" <nicemonitor@hotmail.com> schreef in bericht
>>>> news:1166995538.087683.312160@79g2000cws.googlegroups.com...
>>>> > 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.
>>>> >
>>>
>>
>>
>
>

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

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


Cor Ligthert [MVP] wrote:
> 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

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