PESSIMISTIC WRITE Lock with Isolation level SERIALIZABLE at postgresql

Question

I am getting some errors on some scenario when i am using Pessimistic Write lock with SERIALIZABLE isolation level.

Here is MappingEntity class:

@Data
@Entity(name = "asset_type_mapping")
@Table(
    name = "asset_type_mapping",
    uniqueConstraints =
            @UniqueConstraint(
                name = "UQ_MappingEntity",
                columnNames = {
                    Constants.DATA_TYPE_VALUE,
                    Constants.DATA_TYPE_NAMESPACE_INDEX,
                    Constants.TENANT_ID,
                    Constants.ASSET_TYPE_NAME
                }
            )
)
public class MappingEntity {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    private String id;

    @Column(name = Constants.DATA_TYPE_VALUE)
    private long dataTypeValue;

    @Column(name = Constants.DATA_TYPE_NAMESPACE_INDEX)
    private int dataTypeNamespaceIndex;

    @Column(name = Constants.ASSET_TYPE_NAME)
    private String assetTypeName;

    @Column(name = Constants.TENANT_ID)
    private String tenantId;
}

Here is MappingRepository class:

public interface MappingRepository extends JpaRepository<MappingEntity, String> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    MappingEntity findMappingEntityWithLockByTenantIdAndAssetTypeName(
            String tenantId, String assetTypeName);
}

Here is service code block where i am getting errors.

@Transactional(isolation = Isolation.SERIALIZABLE)
    public void deleteAspectType(String tenantId, String aspectTypeId) {

MappingEntity mappingEntity = mappingRepository.findMappingEntityWithLockByTenantIdAndAssetTypeName(tenantId, assetTypeName);

mappingRepository.delete(mappingEntity);

}

Now let me explain my steps. In my database there is two rows in MappingEntity Table, and i want to delete these two rows at the same time. For that i send two request at same time. Two threads take this request and invoke deleteAspectType() method same time. These two threads first run select query with lock then delete. But one of threads fails to delete, and throw exception:

ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during write.
  Hint: The transaction might succeed if retried.

I am confused. Threads retrieve different rows and lock them. When i change to isolation level to Read Committed, I dont take this exception and works fine. Or when i add to index this table with tenantId and assetTypeName while isolatation level still SERIALIZABLE, I dont take this exception too.

Please somebody explain me, Why am i getting this exception when isolation level SERIALIZABLE without index and why this code works fine when isolation level Read Committed or isolation level SERIALIZABLE with index?

pifor pifor · Accepted Answer · 2020-04-23T18:53:49

This behaviour is likely explained by the fact that transaction do not read the same rows if a index is used or if table sequential scan is used. Doc says

To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first. In PostgreSQL these locks do not cause any blocking and therefore can not play any part in causing a deadlock. They are used to identify and flag dependencies among concurrent Serializable transactions which in certain combinations can lead to serialization anomalies.

And:

A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures. It may be helpful to encourage the use of index scans by reducing random_page_cost and/or increasing cpu_tuple_cost. Be sure to weigh any decrease in transaction rollbacks and restarts against any overall change in query execution time.

PESSIMISTIC WRITE Lock with Isolation level SERIALIZABLE at postgresql

1 Answers