serializable read only deferrable
От | Kevin Grittner |
---|---|
Тема | serializable read only deferrable |
Дата | |
Msg-id | 4CFB574702000025000382FD@gw.wicourts.gov обсуждение исходный текст |
Ответы |
Re: serializable read only deferrable
Re: serializable read only deferrable |
Список | pgsql-hackers |
I'm reviving the discussion on the subject topic because I just had an epiphany which makes it seem simple to implement. The concept of this is that if you start a SERIALIZABLE READ ONLY transaction in an SSI environment when certain conditions are true, it doesn't need to acquire predicate locks or test for rw-conflicts. This would be particularly useful for pg_dump or large reports, as it would allow them to read data which was guaranteed to be consistent with later states of the database without risking serialization failure or contributing to the failure of other transactions. They should also run a bit faster without the overhead of locking and checking. Having completed the switch from a pair of rw-conflict pointers per serializable transaction to a list of rw-conflicts, I'm working through the more aggressive transaction clean-up strategies thereby allowed in preparation for the graceful degradation code. Along the way, I noticed how easy it is to allow a READ ONLY transaction to opt out of predicate locking and conflict detection when it starts with no concurrent non READ ONLY transactions active, or even to remove READ ONLY transactions from those activities when such a state is reached during the execution of READ ONLY transactions; while properly recognizing the *additional* conditions under which this would be valid is rather painful. (Those additional conditions being that no concurrent non-read-only transaction may overlap a committed non-read-only transaction which wrote data and committed before the read-only transaction acquired its snapshot.) The simple way to implement SERIALIZABLE READ ONLY DEFERRABLE under SSI would be to have each non-read-only serializable transaction acquire a heavyweight lock which can coexist with other locks at the same level (SHARE looks good) on some common object and hold that for the duration of the transaction, while a SERIALIZABLE READ ONLY DEFERRABLE transaction would need to acquire a conflicting lock (EXCLUSIVE looks good) before it could acquire a snapshot, and release the lock immediately after acquiring the snapshot. For these purposes, it appears that advisory locks could work, as long as the lock release does not wait for the end of the transaction (which it doesn't, if I'm reading the docs right) and as long as I can pick a lock ID which won't conflict with other uses. That latter part is the only iffy aspect of the whole thing that I can see. Of course, I could add a third lock method, but that seems like overkill to be able to get one single lock. Since I'm already allowing a transaction to opt out of predicate locking and conflict detection if there are no non-read-only transactions active when it acquires its snapshot, the work needed within the SSI code is pretty trivial; it's all in adding the DEFERRABLE word as a non-standard extension to SET TRANSACTION et al, and finding a heavyweight lock to use. Thoughts? -Kevin
В списке pgsql-hackers по дате отправления: