Thread: ACCESS EXCLUSIVE lock
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. Thanks for your suggestions! Regards, Antal Attila
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?
Atesz <atesz@ritek.hu> writes: > My question: Why need this strict locking? > In my opinion there isn't exclusion between the DROP CONSTRAINT and the > SELECT. This isn't going to be changed, because the likely direction of future development is that the planner will start making use of constraints even for SELECT queries. This means that a DROP CONSTRAINT operation could invalidate the plan of a SELECT query, so the locking will be essential. regards, tom lane
Scott Marlowe wrote: > What if, a minute or two after the drop contraint, you issue a rollback? > After the DROP CONSTRAINT I insert 4 million rekords into the TABLE b. After the inserts I remake the dropped constraints, and commit the transaction (P1). This solution is faster then the conventional method without the constraint's trick. In my work the table A is a dictionary table (key-value pairs) with 100-200 records, and the TABLE b has 20 columns with 10 references to TABLE a. So my experience is that I have to drop constraints before the 4 million inserts and remake those after it. If there is an error in my transaction (P1) and I have to rollback, there isn't problem, because my inserts lost from TABLE b and the dropped constraints may be rolled back. In my opinion there isn't exclusion between a dropped constraint (reference from b to a) and a select on TABLE a. If I think well the dropped constraint have to seem in other transation (for example: P2). And it doesn't have to seem in my transaction, because it has already dropped. Thanks your suggestions! Regards, Antal Attila
Tom Lane wrote: > This isn't going to be changed, because the likely direction of future > development is that the planner will start making use of constraints > even for SELECT queries. This means that a DROP CONSTRAINT operation > could invalidate the plan of a SELECT query, so the locking will be > essential. > Hi! I also think the constraints can increase performance of queries, if the planner can use them. It will be a great feature in the future! But I have more questions about the coherency between a constraint and a transaction. Can a constraint live in differenet isolation levels? If I drop a constraint in a transaction (T1), it doesn't seem after the drop operation in T1. But it should seem in another transaction (T2) in line with T1 (if T2 is started between T1's begin and commit!). If T1 start after T1's commit, our constraint doesn't have to seem in T2, so the planner cannot use it. If I think well, these predicates means the constraint follows its isolation level of the transaction. How does it works in the current release? If the constraints adapt its transaction why could it invalidate the plan of a SELECT query? A SELECT could use a given constraint, if it's dropped without comitting or exists when the SELECT or the tansaction of the SELECT starts. I know we have to examine which rows can affect the result of the SELECT. The main question in this case is that: A wrong row (which break the dropped constraint) can affect the result of the SELECT? In my opininon there isn't wrong rows. Do you know such special case when it can happen? So some wrong rows can seem in the SELECT? I know my original problem is not too common, but the parallel performance of the PostgreSQL is very important in multiprocessor environment. I see, you follow this direction! So you make better locking conditions in 8.2 in more cases. Generally the drop constraints are running in itself or in short transactions. We have an optimalization trick when we have to insert more million rows into a table in same transaction. Before inserting them we drop the foreign key constraints after the begin of the transaction, and remake tem after insertations. This method is faster then the conventional solution. These trasactions are longer (5-40 minutes on a SunFireV40z). I read the TODO list and I found more features about deferrability. Would you like to implement the deferrable foreign key constraints? If you want, in my opinion my posings will thouch it. Thank you in anticipation! Regards, Antal Attila