Home » blogs » Spokey's blog

Ask Spokey: Locked Parent Tables While Building Referential Integrity

Submitted by Spokey on Fri, 2008-07-25 14:53. Under: | |

Davorin wrote:

OK Spokey,

you asked for it! :)

We have 3 tables of significant size, whatever that might mean, let's just assume it takes some time to build FK's and underlying indexes.

tab1: col11 (PK)
tab2: col21, col22
tab3: col31, col32, col33

Initially we only have primary key on tab1.col11.
At one point in time we want to add two foreign keys, one on tab2.col21 and another on tab3.col31, both referencing tab1.col11. Let's also assume no apps are connected to the instance/database we work with.
Now, to save time we want to build FK's in parallel so we kick of two separate scripts, one to build a FK on tab2 and another one to build it on tab3.
The result? One goes fine, another reports:

Database selected.

242: Could not open database table (informix.tab1).

106: ISAM error: non-exclusive access.
Error in line 2
Near character position 150

Database closed.

Both FK builds need exclusive access to parent table. My question is simple: why?

Thanks

Davorin

Hi Davorin,

Building a referential constraint against tab1 means that you cannot delete rows from tab1 while the constraint is building, otherwise the constraint might become invalidated halfway through the build.

If we start with tab1 looking like this:

row1
row2
row3
row4
.
.
.
row4972342889

Among the references we want to maintain in tab2 is a reference to tab1.row2. We start building the referential constraint on tab2, but while we are working on row 599969, someone comes along and deletes row2 in tab1:

row1
row3
row4
.
.
.
row4972342889

Although the constraint was valid at the time we were processing the link to tab1.row2, it is now no longer valid. But the engine thinks that it is, because it's already processed tab1.row2 and it was all good at the time.

The easiest way of ensuring that the contents of tab1 don't change is to exclusively lock the table. And, as you know, if one process has an exclusive lock on a table, another process can't get it too.

I hope this helps! (As an aside, have you looked into using fragmentation and PDQ to help your indexes build faster?)

Spokey

» Spokey's blog | login or register to post comments | email this page | Printer-friendly version | 250 reads

Misconceptions

Submitted by dkremenjas on Tue, 2008-07-29 12:02.

Hi Spokey,

I didn't think you'd have the influence in R&D, I was just hoping for an explanation which I got. Again, thank you for all the effort.

I'm somehow reluctant to log a call with Tech Support for the reasons I mentioned above.
Although, if I ever get bored at work, I might do it just for the fun of it :o)
Until then, I have another question, but I'll open it up in a new thread.

Regards

Davorin

» login or register to post comments | email this page

Hi Davorin, I suspect that

Submitted by Spokey on Wed, 2008-07-30 15:19.

Hi Davorin,

I suspect that R&D would be very grateful to you for logging the case, because it gives them a basis for addressing the issue. ;-)

» login or register to post comments | email this page

Hi Davorin, Thank you for

Submitted by dkremenjas on Tue, 2008-07-29 11:52.

Hi Spokey,

thank you for all the effort. This is the answer I feared :) If it was always working like this, I'm not sure if anyone in the R&D is willing to change it.
After all, it is the core functionality, who knows what might broke after changing it.

Cheers

Davorin

» login or register to post comments | email this page

Locked Parent Tables While Building Referential Integrity

Submitted by dkremenjas on Fri, 2008-07-25 16:16.

Hi Spokey,

thanks for the explanation.
I understand it completely, but...

As far as I know exclusive lock on a table is not the same as an exclusive access to the table (meaning open reference to the tablespace/partition as Fernando Nunes explained in detail in one of the first entries in his blog).

Surely, shared lock on a parent table should be enough to protect it from updates/deletes and breaking ref. integrity. Any number of processes (parallel FK builds on child tables) could then place shared lock on the parent and happily proceed with building the FK.

So I guess I should rephrase my question: why is exclusive access mandated in this case when it seems that shared lock on the parent table should be enough?

Thanks a lot!

Davorin

» login or register to post comments | email this page

Hi Davorin, Thank you for

Submitted by Spokey on Tue, 2008-07-29 01:32.

Hi Davorin,

Thank you for clarifying the issue for me. As far as I can tell, this is "working as designed". If you feel that this is not how it should be designed, then I suggest that you log a PMR with technical support and report it as a possible product defect.

Certainly, at the user level, there is no logical reason that I can find to disagree with you, nor can I find anyone in development who can disagree with you either. And I asked a lot of people in development! :-)

» login or register to post comments | email this page

Yep... Why not shared?

Submitted by domusonline on Sat, 2008-07-26 12:23.

Hello Spokey and Davorin

Davorin's question has always kept me wondering...
Somehow it is the same for index building. That is solved by online index creation, but in this situation I don't understand why the shared lock is not enough... If Spokey can't answer this we can try asking inside IBM....

Regards and congratulations to Spokey for this initiative. I really miss "tech" alias :)
Regards,

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com

» login or register to post comments | email this page

Yep... Why not shared?

Submitted by dkremenjas on Mon, 2008-07-28 13:00.

Hi Fernando,

Could you please escalate this inside IBM? That would be great. I'd appreciate it very much. You know it's very hard to get an answer from front line support on hard topics like this. And this is real world problem, not just a curiosity. Just imagine a complete disk re-layout of 300+ GB database in minimum downtime in ER/HDR environment.
Mixture of HPL and SQL unloads/loads, index builds etc...and the only thing I can't optimise/parallelise - foreign key builds...

Thanks.

Davorin

» login or register to post comments | email this page

Davorin, There is a common

Submitted by Spokey on Tue, 2008-07-29 01:36.

Davorin,

There is a common misconception that IBM insiders have mystical influence in development. In fact, nothing has more power than a customer reporting a Feature Request, unless it's lots of customers reporting the same Feature Request! ;-)

I'd strongly recommend that you raise this as an issue. We will do what we can to encourage addressing this issue, but your input as a customer is crucial.

I have done a lot of research into your question, and my reply is above, in response to your original clarification.

» login or register to post comments | email this page