Re: Detecting Table Locks?

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

.

Relevant Pages

  • Re: row vs page locking...
    ... so they automatically escalate to page level locks. ... 'Lock Escalation' - see Books Online. ... SQL Server 2005 is going to have a Snapshot ... Good old fashioned locking is less sexy, but I find, more productive! ...
    (microsoft.public.sqlserver.server)
  • Re: sqltransaction timeout
    ... SQL Server places exclusive locks on all of the resources ... These locks are then held until the ... transaction either commits or rolls back. ... SQL Server does not even ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: After SP4, proc w. Cursor doesnt release keylocks
    ... May Hold Locks Longer http://support.microsoft.com/kb/260652/ ... As a shot in the dark, if the cursor declaration just says: ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: One bound form does NOT save record - please help!
    ... on the table on the SQL side that might be causing a problem? ... The locks can be viewed under ... Start app with decompile switch, ... Restart the app without decompile switch, ...
    (comp.databases.ms-access)
  • Re: Table locking in MS SQL Server
    ... There are many articles on internet for preventing escalating locks. ... > In the SQL log, generated by the front-end application, we see SQL ... > When there are big delays on the LAN, the system (server) tends to freeze ... > (However, in a server trace, I see automatic transactions.) ...
    (microsoft.public.fox.vfp.queries-sql)
Loading