Re: postgresql locks the whole table!

Greg Stark wrote:
>
> Dr NoName <spamacct11(at)yahoo(dot)com> writes:
>
> > My question is why??? The two insert operations do not
> > conflict with each other (at least not in the
> > real-world situation). Also, why does the foreign key
> > make a difference?
>
> It's not locking the whole table, it's locking the record that the foreign key
> references. Note that they're both referencing the same foreign key.
>
> It does this because it's afraid someone will go and delete that key before
> the transaction commits. It has to take a lock that will prevent someone from
> deleting the record (or updating the referenced column).
>
> Unfortunately the only lock to choose from is an exclusive write lock. That's
> overkill as you've noticed. I think this is something multiple people would
> like to fix by introducing shared locks, but I wouldn't expect a solution
> soon.

As I remember the implementation problem is that we do an exclusive row
lock right now by putting the transaction id on the row and set a
special row-lock bit in the tuple. Shared locks need to store multiple
transaction ids, and that is where we are stuck. Shared memory is of
finite size, and the number of proc/row combinations is infinite, so it
seems we will need some shared stucture with backing store to disk, and
that will be slow.

You know the maximum number of backends on startup, but I don't see how
that helps us. If we could somehow know the unique combinations of
those backend ids that would be used for any row, we could reserve a
range of transactions ids to map them, but the number of combinations is
too large.

Our xid/command-counter is currently 64 bits, so if we only had a
maximum of 64 backends, we could use those bits to mark the backends
holding the locks rather than put the xid in there. Of course, the
maximum number backends can change over time, so that isn't really a
solution but more a brainstorm, but I do think shared memory bitmaps
might be in the final solution.

One idea would be to allocate 10k of shared memory for a shared lock
bitmap. Assuming a max 100 backend, that is 2500 lock combinations,
numbered 0-2499. We would put the bitmap number on the rows rather than
the xid. Of course, problems are that there are only 2500 combinations
supported, and transactions have to get an exclusive lock on transaction
commit to clear their backend bits from the bitmap table so the rows can
be reused. Another refinement would be to use the row xid to store
either the xid for single backend locks, and use the bitmap table number
only when there is sharing of row locks by multiple backends. That
might reduce the contention on the bitmap table. If a backend wasn't
involved in shared locks, its bit wouldn't be set in the bitmap table
and it has nothing to do, and it can read the table without a lock.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073