locking

locking

Post by ascut.. » Sat, 14 Aug 1999 04:00:00



Our employer wants a pessimistic locking schema using ejb's with
internet options in the future.
after investigating the nature of EJB and the net We feel this to be a
mistake.
But we are NEWBIES What technical arguments are there against
pessimistic locking / JAVA / EJB and the internet / intranet

Thanks the scutters

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

1. Strange lock issue (why 400,000 locks are needed when 1 will do)

I have a strange lock problem.  I have searched groups.google.com and
microsoft.com with no luck.

Environment:  NT 4.0 Server with Service pack 6a.  SQL Server 7 with
service pack 3.  On a Compaq server with  512 MB ram, dual PIII 500
CPU and a SCSI RAID 5 array.

We have a stored procedure that adds new incidents to our log table.
We attempt to match with an existing incident first (via an update),
then if we can't find a match we add it (via insert).  In normal
system operation this procedure runs at least 30 times a minute
(depends on the load of the system).  The procedure runs great and the
system runs great.  But sometimes SQL Server gets into a strange state
where we start getting timeouts (we set the timeout via the ODBC API
for 10 seconds on this particular command).

I have been able to track it down to the update that is locking up. If
I run a sp_lock when this procedure is running I find tons of row
locks for the spid that is running the stored procedure (400,000+ in
one case a couple of days ago).  The update (ie match part) can only
effect one row at the most (normal operation would affect 0 rows), so
I am at a loss at why something that should only create 1 lock (maybe
a few other locks at the most) would create 400,000+ locks.

Another thing is if I take the update statement that timeouts and run
it with the same data via isqlw (therefore running in a separate
connection) it runs fine (with sub-second response).  If I run the
stored procedure (with contains the exact same update statement) in
the same isqlw window  it takes 20 seconds (which is 19.9 seconds too
many).  The only way I can get things working again is to restart SQL
Server (which is unacceptable).

Has anybody seen this? Thanks in advance for any help.

2. postgresql hangs on query ....

3. Lock request timeout when viewing locks

4. What Libraries to include??

5. Locking/Locked records

6. Win32 API calls: opening & closing applications

7. help with record locking.. (Pessimistic locking)

8. Number of levels in a clustered and non-clustered index

9. How to lock and detect the locked record?

10. Pessimistic locking and Lock escalation

11. No Deadlocks...Lock file locks

12. No DeadLocks ...Lock file locks

13. Locking Problems (Need more locking info from SQL)