On Wed, 2006-10-18 at 09:24, Atesz wrote: > Hi! > > I have a problem with ACCESS EXCLUSIVE lock when I drop a reference in > transaction. I have 2 tables: > create table a(id SERIAL primary key); > create table b(id SERIAL primary key references a(id)); > > After that I have 2 processes: P1, P2 > In P1: > begin; > ALTER TABLE b DROP CONSTRAINT b_id_fkey; > > In P2: > SELECT * FROM a; > > And I'm waiting for the result, but I don't get until P1 finishes. > I know the DROP CONSTRAINT put an ACCESS EXCLUSIVE table LOCK into the > TABLE a, and the SELECT is stopped by this LOCK in P2. > Note: I cannot commit the P1 earlier, because it's a very long > transaction (more hours, data conversion transaction) > My question: Why need this strict locking? > > In my opinion there isn't exclusion between the DROP CONSTRAINT and the > SELECT. What if, a minute or two after the drop contraint, you issue a rollback?
