Обсуждение: AW: AW: Issue NOTICE for attempt to raise lock level?
> >> I am working on eliminating the "relation NNN modified while in use" > >> misfeature by instead grabbing a lock on each relation at first use > >> in a statement, and holding that lock till end of transaction. > > > As anticipated, I object :-) > > Your objection is founded on two misunderstandings. In the first place, > we are *always* inside a transaction when executing a query. It may be > an implicit one-statement transaction, but it's still a transaction. Ok, but I thought there was some optimization for readonly statements. > In the second place, we already grab locks that we do not release till > end of xact for all user-level queries. The problem is that we grab > them too late, ie, in the executor. I'm just planning to move up the > grab till first use. For a "select colname from tablename" we do not currently hold any lock until end of tx. This is the situation you described, and I am worried about. Andreas
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes: > Ok, but I thought there was some optimization for readonly statements. Doesn't have anything to do with locking, only with avoiding disk writes. >> In the second place, we already grab locks that we do not release till >> end of xact for all user-level queries. The problem is that we grab >> them too late, ie, in the executor. I'm just planning to move up the >> grab till first use. > For a "select colname from tablename" we do not currently hold any lock > until end of tx. This is the situation you described, and I am worried about. That's a bug in itself, because the executor's read lock is grabbed by heap_beginscan and released by heap_endscan, which means it may be grabbed and released multiple times during a single query (think nested-loop join). There is nothing to stop someone from, say, dropping the entire table between scans. I intend to fix that. I am not nearly as worried about long-running transactions that delay admin actions as I am about admin actions that crash other transactions. I do not believe it is safe to drop read locks intra-transaction, and I am unwilling to take a chance on it being safe so close to 7.1 beta. We can argue about it when 7.2 development cycle starts, if you like. regards, tom lane
Tom Lane wrote: > I am not nearly as worried about long-running transactions that delay > admin actions as I am about admin actions that crash other transactions. > I do not believe it is safe to drop read locks intra-transaction, and > I am unwilling to take a chance on it being safe so close to 7.1 beta. Will we still have readers-dont-block-writers behaviour? i.e. will the read lock lock only rows visible to current transaction ? Or am i talking about something completely different ? ------------- Hannu
Hannu Krosing <hannu@tm.ee> writes: > Tom Lane wrote: >> I do not believe it is safe to drop read locks intra-transaction, and >> I am unwilling to take a chance on it being safe so close to 7.1 beta. > Will we still have readers-dont-block-writers behaviour? Sure. The only thing this really affects is VACUUM and schema-altering commands, which will now have to wait until reader transactions commit. In other words Session 1 Session 2 BEGIN;SELECT * FROM foo; ALTER TABLE foo ... ... COMMIT; Session 2 will have to wait for session 1 to commit; before it didn't. An example of why this is a good idea is Session 1 Session 2 BEGIN;DECLARE c CURSOR FOR SELECT * FROM foo; ALTER TABLE foo ... FETCH FROM c; COMMIT; Without a held read lock on foo, session 1 is in deep trouble, because its cursor is no longer correctly planned. regards, tom lane