Re: Isolated transactions?

On Tue, Oct 04, 2005 at 04:46:04PM -0400, Douglas McNaught wrote:
> Lexington Luthor <lexington.luthor@gmail.com> writes:
> > I have a number of large tables in a schema all of which are related
> > by foreign keys.
> >
> > Now, what I would like to be able to do is run some long-running
> > queries (mostly selects, but also some updates and inserts into the
> > tables), to generate some reports which will eventually be
> > rolled-back.
> >
> > Now, while this works just fine, I would like to be able to avoid
> > holding other transactions back while this is happening.
>
> In general, you won't block other transactions, unless you do explicit
> locking with SELECT FOR UPDATE or LOCK TABLE.  The rows you change
> will simply never be seen by other transactions and will be removed by
> the next VACUUM after your transaction rolls back.

Updates will block other update attempts on the same rows.  Also,
inserts or updates on records with foreign keys acquire locks on
the referred-to keys that can block other operations that you might
not expect.  For example:

  CREATE TABLE foo (
      id   integer PRIMARY KEY
  );

  CREATE TABLE bar (
      id     integer PRIMARY KEY,
      fooid  integer NOT NULL REFERENCES foo
  );

  INSERT INTO foo (id) VALUES (1);

If Transaction A does this:

  BEGIN;
  INSERT INTO bar (id, fooid) VALUES (1, 1);

and then Transaction B does this:

  BEGIN;
  INSERT INTO bar (id, fooid) VALUES (2, 1);

then Transaction B will block even though there's no apparent
conflict.  This is a gotcha that can cause deadlock errors (8.1
will acquire a weaker lock, which should reduce the likelihood of
deadlock).

--
Michael Fuhr