could not serialize access due to concurrent update

The following error message from YugabyteDB may not be clear if you don't know how isolation levels and read/write consistency works in YugabyteDB:

pq: could not serialize access due to concurrent update (query layer retry isn't possible because data was already sent, if this is the read committed isolation (or) the first statement in repeatable read/ serializable isolation transaction, consider increasing the tserver gflag ysql_output_buffer_size)

Here is an explanation I've given to a user, which may help others get the whole picture.

Your application performs a transaction that involves both reading and writing data. It is crucial that reads occur from a consistent state.

If you are using repeatable read/ serializable isolation transaction, this consistent state, the read time, is established at the start of the transaction. If using read committed isolation level, it is established at the start of each statement

You can verify the current isolation level by executing SELECT current_setting('yb_effective_transaction_isolation_level').

The read committed isolation level is generally sufficient, except in cases where initial reads impact subsequent updates. For example, if you read a list of available seats on a plane without using the 'FOR UPDATE' clause, and then attempt to book a seat afterward, you want to ensure your update fails if someone else booked that same seat in the interim. This scenario requires the repeatable read isolation level.

When writing, the application commits to the state as it exists at the time of the commit, corresponding to the end of the transaction. All write operations, including updates or the reads that use the 'SELECT FOR UPDATE' clause, lock the data they modify to ensure the state written to remains until the transaction is complete. That's how the state at the time of commitment matches the state at the time of the writing, but it's still a higher point in time than the read state.

To avoid readers blocking writers, the same (locking) doesn't happen for read, and the state that is read may be modified by other transactions. Your transaction must detect it because it cannot commit a write state that conflicts with the read state. Think about time-traveling movies where modifying the past makes the future inconsistent.

Suppose another transaction alters and commits a row that you have read and write later. The read and write states are inconsistent. A conflict will be detected in that case, resulting in an error message stating could not serialize access due to concurrent update. This occurs because committing both transactions would create inconsistency.

The likelihood of encountering such a conflict is more significant in the repeatable read isolation level than in read committed since the reading duration is longer (the duration of the entire transaction as opposed to a single statement).

If the conflicting statement is the first statement of the transaction, the database can roll back and restart using a newer read time without raising an error. If it is not the first statement, it can still restart at the read committed isolation level because this level allows for a newer read time. An implicit savepoint is taken before each statement. This means using read-committed isolation or being at the first statement in a repeatable read or serializable isolation transaction.

Another scenario in which the database cannot transparently roll back and restart is if data has already been sent to the application. The application may have performed non-transactional actions based on that data (such as writing to a file, sending an email, or pushing to a queue). The database must raise an error in such cases, as only the application can cancel or compensate for those non-transactional actions. Transparent query layer retry isn't possible because data was already sent.

Data returned to the application is buffered, but this situation may arise if a query returns many rows. You can mitigate this issue by increasing the tserver gflag ysql_output_buffer_size when starting the cluster.

Here is how to get the error when it is not the first statement:

yugabyte=> \set VERBOSITY verbose
yugabyte=> create table demo (id int primary key, value int);
CREATE TABLE
yugabyte=> insert into demo values (1,0),(2,0) ;
INSERT 0 2
yugabyte=> begin transaction isolation level repeatable read;
BEGIN
yugabyte=*>  select * from demo where id=1;
 id | value
----+-------
  1 |     0
(1 row)

yugabyte=*> -- another transaction
yugabyte=*> \! psql -c 'update demo set value=value+1 where id=2'
UPDATE 1
yugabyte=*>  -- not the first statement in repeatable read/ serializable isolation transaction
yugabyte=*>  update demo set value=2 where id=2 returning *;

ERROR:  40001: could not serialize access due to concurrent update (query layer retry isn't possible because data was already sent, if this is the read committed isolation (or) the first statement in repeatable read/ serializable isolation transaction, consider increasing the tserver gflag ysql_output_buffer_size)
DETAIL:  Conflict with concurrently committed data. Value write after transaction start: doc ht ({ physical: 1736852963319098 }) >= read time ({ physical: 1736852963105679 }), key: SubDocKey(DocKey([], [2]), []): kConflict

LOCATION:  conflict_resolution.cc:963

The SQLSTATE is 40001, a retryable error. You can implement a retry logic, but it is also essential to understand how it happened. This blog post explained all the terms in the error message and described what is behind it.