Shalini wrote:
> Could you also please state the reason why is it happening in case
> of large objects? Because concurrent transactions are very well
> handled for other data types, but the same is not happening for
> lobs. Is it because the fomer are stored in toast table and there is
> no support for concurrent txns in pg_largeobject table?
Keeping in mind that large objects are not a datatype, but rather a
functionality that is built on top of the bytea and oid datatypes plus
a set of functions, I wouldn't say that concurrent writes would be
better handled if you had a table: document(id serial, contents bytea)
with "contents" being indeed toastable.
To illustrate with a basic example: transactions Tx1 and Tx2
want to update the contents of the same document concurrently,
with this order of execution:
Tx1: begin
Tx1: update document set contents=... where id=...
Tx2: begin
Tx2: update the same document (gets blocked)
Tx1: commit
Tx2: commit
If using the read committed isolation level, Tx2 will be put to wait
until Tx1 commits, and then the update by Tx1 will be overwritten by
Tx2. That's a well known anomaly known as a "lost update", and
probably not what you want.
If using a better isolation level (repeatable read or serializable),
the update by Tx2 will be rejected with a serialization failure,
which, to me, seems the moral equivalent of the "Tuple concurrently
updated" error you're reporting with large objects.
When this occurs, your application can fetch the latest value in a new
transaction and see how it can apply its change to the new value,
unless another conflict arises and so on.
In short, the best the database can do in case of conflicting writes
is to inform the application. It can't know which write should be
prioritized or if the changes should be merged before being written.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite