Re: OnLine 5.06 Locking Query
rajivp@qsun2.ho.att.com wrote: > > > From rmy.emory.edu!informix-list-owner@kcig2.att.att.com Fri Aug 23 09:47:56 1996 > > >Received: by kcgw2.att.com; Fri Aug 23 08:41 CDT 1996 > > From: Sally Woolrich <sally@excelsis.co.uk> > > Subject: OnLine 5.06 Locking Query > > Sender: informix-list-owner@rmy.emory.edu > > To: informix-list@rmy.emory.edu > > X-Informix-List-To: rajivp@qsun.ho.att.com > > X-Informix-List-Id: <news.27380> > > Content-Type: text > > > > I have been told that deleting a unique key from a table locks the > > *whole* table! Any comments on this? > > > > TIA > > > > -- > > Sally Woolrich > > > > > > Sally, > > This can be true if > An exclusive lock is placed on the table using LOCK TABLE > before starting a delete. > > If the table has PAGE level locking then that all the rows on that page > would be locked when the delete is in progress. > > At least this is what I think should happen, but I may be in for a suprise > on this one. > > -- > Rajiv Jhaveri > DBA > > ================================================================= > Voice : (908) 949 7987 Pager : 544 7872 or > 1-800 225 0256 PIN 310902 > Fax : (908) 949 1726 E-mail: rajivp@qsun.ho.att.com > =================================================================Hi Sally and Rajiv There is certainly no table level lock when deleting a unique key, however there are a few interesting quirks which could happen under these circumstances. 1) If deleting the row requires that the index structure change, the previous and next index page are locked, effectively locking all of the rows on three pages. 2) Even with row level locking, I believe that the row before the current row is locked (somebody should check me on this, however). The reason is that the db needs to create a pointer between the previous row and the next row (since you are deleting the current row) and if someone were to, say delete the previous row during this process, I think the db wouldn't know what to do. Once again, I'm not 100% on this, it's an explanation which was given to me for locking problems we had under 5.04. Jason Freedman jfreedman@andersen.com