Search code examples
postgresqlconcurrencyrace-condition

postgresql concurrent write on the same row

I have multiple process running on the same postgresql db, when multiple process tries to write same row (different columns its updating) is below flow is correct? isolation level - READ COMMITTED

enter image description here

Im fine with process-2 waiting for the process-1 completes just want to make sure i dont lose any data or it throws any errors.

Solution

  • Yes, you described the behavior correct. The data modifications get serialized by row locks, so process 2 has to wait until process 1 commits the transaction. On the READ COMMITTED transaction isolation level, process 2 will then fetch the latest committed version of the row, check if it still qualifies, lock it and perform the update.

    In short, process 2 won't undo the data modification of process 1.

    While this behavior is convenient and efficient, it can lead to interesting anomalies because it makes process 2 operate on a later version of the data than it could normally see. Higher transaction isolation levels avoid that problem by reporting a "serialization error" to process 2, which then has to repeat the transaction.