Re: Fix FK deadlock, but no magic please

Thanks Stephan for you quick response.

On Thursday 16 January 2003 10:55 am, Stephan Szabo wrote:
> On Thu, 16 Jan 2003, Jon Swinth wrote:
> > The terms "dirty read" and "magic" came up during the description of the
> > fix. The term "dirty read" is a dirty phrase when your using proper
> > transactioning. The term "magic" is not what you want to hear when the
> > database is supposed to be the rock that everything else depends on.
>
> Well, I used magic flippently as short for, I haven't worked out the
> details yet because I'm in the sit down design and try things out stage.
> I'll apologize for using the term then.
>
> As for dirty reads, given that part of the part that was referred to as
> magic involves doing waits on transactions so that it's very much like
> the current row locks except with foreign key based knowledge embedded
> so as to help avoid deadlocks. Yes, it's seeing rows that haven't
> been committed, but it's mostly seeing them to find out what transactions
> it needs to wait on.
>

Fair enough.

> > Other databases have tackled this issue without the above terms. From
> > what I can tell, there is a standard and non-standard way this can be
> > fixed in PostgreSQL. The standard way would be to implement FK as a part
> > of the schema and create the hooks to allow read locks on records by FK
> > only. The non-standard way would be to expand the SQL interface with a
> > non standard FOR READ statement (or something similar) and then continue
> > to use triggers. Only the developers can decide which method will be
> > easier, work better, or is more in line with the overall goals of
> > PostgreSQL.
>
> Record read locks are not quite as good a solution as dirty reads from a
> performance standpoint, which is why we've been aiming that direction
> first. You'd need column locks pretty much to get equivalent behavior
> afaict. The issue is that with record read locks, you prevent updates to
> rows that do not affect the key values.
>

From the standpoint of expected behaviour, I don't think you have any choice
but to use record read locks. When someone does a write lock on a FK table
record they have the expectation that they can do anything they want with the
record including changing the PK or deleting the record. That is as long as
there were no referencing records before the write lock was obtained. This
means that someone else shouldn't be able to insert a record referencing
while the FK table record has a write lock.

Not being able to get a read lock when someone else has a write lock is
expected behaviour. A single record should be able to have one write lock or
multiple read locks, but not both. If I have a program that checks for
referencing records, deletes them if found, and obtains a write lock on the
FK record then I should reasonably assume that I can change anything about
that record including delete it. If you don't prevent the write lock when a
read lock is there then the person obtaining the write lock to very well get
errors that they wouldn't normally expect.

> > I've even tried to get an estimate from pgsql.com for this issue, but
> > they just ignored me. I figure that the alternative is to get Oracle
> > which has a price tag equivalent to at least 20 man weeks of effort
> > (minimum). I'd much rather see this kind of money go toward making
> > PostgreSQL better. I don't know if I can actually get the money, but I
> > would at least like to know what to shoot for. Maybe I can get multiple
> > customers to split the fee.
>
> I personally can't (I have a full time job that has nothing to do with
> PostgreSQL which is part of why this hasn't gotten done yet), but I
> certainly wouldn't want that to stop someone who does have the time and
> could take the money from doing it instead. :)