[SOLVED] Postgress Serialisation Error For SKIP Locked with repeatable read

Postgress Serialisation Error For SKIP Locked with repeatable read

I am running a query with skip locked

select * from booker.review_tasks where review_task_id in (
    2140285001,
2140285031,
2140304551
    ) for update skip locked ;

And then updating it using another concurrent transaction

update booker.review_tasks set priority = 190 where review_task_id  in (2140285001,
2140304551);

I am getting an error in the first transaction as

was aborted: ERROR: could not serialize access due to concurrent update  Call getNextException to see other errors in the batch.

    at com.amazon.contentreviewplatformservice.db.RWNamedJdbcTemplate.batchUpdate(RWNamedJdbcTemplate.java:126) ~[ContentReviewPlatformService-1.0.jar:?]

    at com.amazon.contentreviewplatformservice.db.dao.DAOUtils.changeUserTaskAssignmentStatus(DAOUtils.java:126) ~[ContentReviewPlatformService-1.0.jar:?]

    at com.amazon.contentreviewplatformservice.workflow.dao.WorkflowManagerDAO.changeReviewState(WorkflowManagerDAO.java:784) ~[ContentReviewPlatformService-1.0.jar:?]

How can a row be updating when its locked , or if locked is skip why is there error of concurrent update

Solution

The SELECT ... FOR UPDATE first selects the rows and then locks them. It sees the database in the state of its transaction snapshot, which was taken by the first statement in the REPEATABLE READ transaction, possibly a while back.

After the SELECT ... FOR UPDATE has found a row, it will try to lock it. Now if the UPDATE has run at any time between the time the snapshot was taken and the attempt to lock the row, PostgreSQL determines that the row version it would have to lock is different from the row version the transaction sees and throws a serialization error.