RE: locking issues

Rich,

Having assisted a couple of clients experiencing severe application
"hanging" issues (ended up being due to BMI's and locking issues), I
bookmarked the following Notes on MetaLink for their reference. These notes provide a little more detailed information regarding BMI's and locking issues:

Note:70067.1   All about Bitmap Indexes
Note:62354.1   TX Transaction locks - Example wait scenarios
Note:113367.1  Oracle 8i tuning tips using OLAP products

For example, a quote from note 70067.1:

  The smallest amount of a bitmap that can be locked is a bitmap   segment, which can be up to half a data block in size. Changing the   value of a row results in a bitmap segment becoming locked, in effect   blocking changes on a number of rows.

One thing to take from the above statement is that an entire table isn't necessarily locked. And the developer, Kevin, working with Joe on the problem, and responding to the list, made that distinction clear. He tied the locking to the BMI and rows it covered, not the entire table. So, while you didn't bring it up, a DML action against a table with a BMI doesn't necessarily lock the entire table. Sorry, just to clear up some potential confusion from earlier posts on this subject where people may have gotten the impression that the entire table would always be locked.

FWIW, and I think the Kevin said as much, getting rid of the BMI's didn't completely resolve their particular deadlock issue, though it was part of the / a problem. When I have run across BMI locking issues, they have manifested themselves as blocking locks, not as deadlocks resulting in a trace file being generated. I guess what I am getting at is that I don't think the use of a BMI alone can cause a deadlock, that there have to be other factors involved. Then again, I don't think I am qualified to say BMI's can *never* cause a deadlock simply by being BMI's ;-) The same clients that experienced the hanging issues, resolved once the BMI thing was addressed, still experienced deadlock problems due to some other coding issues. Regardless, the use of BMI's in OLTP apps can cause severe locking problems. Oh well, beat a dead horse why don't I ;-)

Regards,

Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jesse, Rich Sent: Tuesday, August 08, 2000 5:08 PM
To: Multiple recipients of list ORACLE-L Subject: RE: locking issues

Hey! Thanks all for the bitmap locking info! :)

I just recreated a bitmap index into a normal one. Hopefully all those
"deadlock-detected" e-mails and trace files will go away now!

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             QTI -- Sussex, WI USA