question

sefas avatar image
sefas asked

Lock in Mssql, What are the (updlock,holdlock,rowlock) use for?

I have some question about Pessimistic Lock on Mssql server? Here is my classes and test scenario;

Entity 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;
}

Repository class:

public interface MappingRepository extends JpaRepository<MappingEntity, String> {

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

Service code block:

@Transactional
    public void deleteAspectType(String tenantId, String aspectTypeId) {

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

mappingRepository.delete(mappingEntity);

}

When I enable the hibernate logs. I see select query below.

select
            mappingent0_.id as id1_1_,
            mappingent0_.asset_type_name as asset_ty2_1_,
            mappingent0_.data_type_namespace_index as data_typ3_1_,
            mappingent0_.data_type_value as data_typ4_1_,
            mappingent0_.tenant_id as tenant_i5_1_ 
        from
            asset_type_mapping mappingent0_ with (updlock,
            holdlock,
            rowlock) 
        where
            mappingent0_.tenant_id=? 
            and mappingent0_.asset_type_name=?

I have sent two delete request at the same time with same tenant_id but different asset_type_name;

Transaction-1: tenant_id = "testtenant", asset_type_name = "testname1"

Transaction-2: tenant_id = "testtenant", asset_type_name = "testname2"

Transaction-1 run select query and gets results, When Transaction-2 run select query it blocks. After Transaction-1 deletes and finishes the transaction, Transaction-2 get results and deletes.

I have two question;

1-) What are the (updlock,holdlock,rowlock) use for? When i use these three same time, how does effect my query and transaction?

2-) Why did Transaction-2 block when it run the query? Because Both transaction selected different rows.

mssqllocking
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.