Question 254308 in PostgreSQL Question posted in
The official documentation can be found here.

Can nonrepeatable reads / phantom reads occur with PostgreSQL's cursors?

When paginating without cursors, it’s possible to see phantom reads. E.g. paginating over a large set of data like so:

// assume isolation level = READ COMMITTED
SELECT * FROM users ORDER_BY age ASC LIMIT :pageSize OFFSET :page * :pageSize

can result in duplicates or missing items if concurrent transactions make changes to the data in between page calls – i.e. triggering phantom reads or nonrepeatable reads.

Do cursors in PostgreSQL exhibit the same behaviour? E.g. if I use a "read committed" isolation level, can fetching from the cursor result in phanton/nonrepeatable reads, causing items to be missed or returned more than once?

Tags:

2

Answers


  1. 0

    There cannot be any phantom reads if you paginate a result set using a cursor, because a cursor uses a fixed snapshot of the data, that is, it always sees the same state of the database, regardless of the life time of the cursor and any concurrent data modifications.

    Note, however, that a regular cursor only lives withing the context of a database transaction. So if you paginate a query result, and the user browses through the result set interactively, you’d need to keep a database transaction open for a very long time. That is something you cannot do, if you value the health of your database. A workaround that you can choose is a WITH HOLD cursor. Such a cursor lives longer than a database transaction, so you don’t need to keep a transaction open. The result set of a WITH HOLD cursor is materialized on the server when the database transaction ends, so that commit can take a long time. You also have to remember to close the cursor, else the resources on the server are bound until the end of the database session.

    See my article for details and further considerations.

    Login or Signup to reply.
  2. 0

    When used with a READ COMMITTED isolation level, PostgreSQL cursors are less likely to result in phantom reads and nonrepeatable reads than straightforward paginated queries. Cursors offer improved isolation from concurrent alterations by maintaining a consistent snapshot of the data depending on the transaction’s status at the time the cursor was opened. However, any data changes you make throughout your transaction could still have an impact on how the cursor behaves.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search