title: Node.js, transaction coflicts in PostgreSQL, optimistic concurrency control and transaction retries
body: <p>I want to use <a href="https://www.postgresql.org/docs/current/transaction-iso.html" rel="noreferrer">PostgreSQL transaction isolation</a> to ensure data correctness with  <a href="https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html" rel="noreferrer">optimistic concurrency control</a> pattern where conflicting transactions are automatically retried, instead of my application doing upfront locking of database rows and tables. </p>
  
  <p>One usual way to implement this is that the web application <a href="https://stackoverflow.com/questions/52434925/what-specific-exceptions-represent-a-serialization-failure-when-django-is-using">retries the transaction</a> a specific number of times within a code block or <a href="https://docs.pylonsproject.org/projects/pyramid_tm/en/latest/#retries" rel="noreferrer">replays the HTTP request</a> by a middleware layer, also known as HTTP request replay. <a href="https://github.com/Pylons/pyramid_tm/blob/master/pyramid_tm/__init__.py" rel="noreferrer">Here is an example of such a middleware for Pyramid and Python web applications web</a>. </p>
  
  <p>I did not find any good information on how Node.js and its PostgreSQL driver handle situations where there are two concurrent transactions in progress and one cannot go through because of reading and write conflicts. PostgreSQL will rollback one of the transactions, but how this is signalled to the application? In Python, PSQL driver would raise <code>psycopg2.extensions.TransactionRollbackError</code> under this condition. <a href="https://github.com/zopefoundation/zope.sqlalchemy/blob/master/src/zope/sqlalchemy/datamanager.py" rel="noreferrer">For other SQL database drivers here are some exceptions they will raise</a>.</p>
  
  <p>This behaviour is more usual when you have set your SQL transaction isolation level to SERIALIZABLE, as you tend to get more conflicts under load, so I would like to handle it gracefully instead of giving HTTP 500 to users.</p>
  
  <p>My question is:</p>
  
  <ul>
  <li><p>How to detect dirty read rollbacks with PostgreSQL and some of the common ORM frameworks like TypeORM - if special handling is needed and the retry library cannot be independent?</p></li>
  <li><p>Is there a middleware (NestJS/Express.js/others) to handle this and automatically try to replay the HTTP request N number of times whenever there is a transaction rollback from the database driver?</p></li>
  </ul>