Blocking Locks

[DirtyB]

Hi all...

I'm fairly new to Oracle and am experiencing extreme problems with users locking records and blocking out others. Our software vendor has suggested we run the ANALYZE (compute statistics) and this will fix our blocking locks. Does anyone see a correlation here? We have run it, and are experiencing no more blocking problems, but I am just trying to understand conceptually how the analyze has relieved this issue.

Thanks

 

[karluk]

Analyzing tables by itself doesn't prevent locking problems. What it does is give Oracle more information to allow queries to run efficiently. That should in general reduce the amount of time a query needs to hold a lock, benefitting other users.

To be honest, though, I think it's most likely that what the vendor explained as a locking problem was in reality a performance problem. Now that things are running more efficiently, I suppose everyone is happy regardless of what the true explanation was.

 

[DirtyB]

Thanks for the info.

I agree, I was in TOAD watching the "blocking locks" and SEVERAL people were being held up. Now, we've ran the Analyze, and haven't had one locking block all day. The vendor did think that I was refering to a performance problem, in reality it was a locking issue....but the fact remains, I haven't seen one blocking lock since we ran the analyze.

And you're right, everyone is happy now...i'm just new to Oracle and trying to learn more on how this fixed the problem.

Thanks again for your input.

 

[Turkbear]

It is rather odd to get locking problems with a well-designed Oracle application..Writes do not block reads and reads do not block writes ( The Oracle mantra)

The application must be using its own locking methods
and when multiple updates are being done to the same record, locks can block...

 

[carp]

Right. And that's because queries don't throw locks (unless it's a "FOR UPDATE OF" query). And normal DML statements don't throw an exclusive lock on a table, so queries can play on through. So it would appear that either (a) some custom code is throwing locks in an unfortunate way or (b) the blocking you saw was for several DML statements stacking up while waiting to get a row-level lock. This could be occurring frequently and you're not looking at the right time or else it may have been a one-time good deal. Either way, doing an ANALYZE shouldn't have much effect one way or another.