title: Updlock, Holdlock, Rowlock usage? Pessimistic Locking in SQL Server
body: <p>I have some question about Pessimistic Locking in SQL Server? Here are my classes and test scenario;</p>
  
  <p>Entity class:</p>
  
  <pre><code>@Data
  @Entity(name = "mapping")
  @Table(
      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;
  }
  </code></pre>
  
  <p>Repository class:</p>
  
  <pre><code>public interface MappingRepository extends JpaRepository&lt;MappingEntity, String&gt; {
  
      @Lock(LockModeType.PESSIMISTIC_WRITE)
      MappingEntity findMappingEntityWithLockByTenantIdAndAssetTypeName(
              String tenantId, String assetTypeName);
  }
  </code></pre>
  
  <p>Service code block:</p>
  
  <pre><code>@Transactional
      public void deleteAspectType(String tenantId, String aspectTypeId) {
  
  MappingEntity mappingEntity = mappingRepository.findMappingEntityWithLockByTenantIdAndAssetTypeName(tenantId, assetTypeName);
  
  mappingRepository.delete(mappingEntity);
  
  }
  </code></pre>
  
  <p>When I enable the hibernate logs. I see select query below.</p>
  
  <pre><code>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
              mapping mappingent0_ with (updlock,
              holdlock,
              rowlock) 
          where
              mappingent0_.tenant_id=? 
              and mappingent0_.asset_type_name=?
  </code></pre>
  
  <p>I have sent two delete request at the same time with same tenant_id but different asset_type_name;</p>
  
  <p>Transaction-1: tenant_id = "testtenant", asset_type_name = "testname1"</p>
  
  <p>Transaction-2: tenant_id = "testtenant", asset_type_name = "testname2"</p>
  
  <p>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.</p>
  
  <p>I have two question;</p>
  
  <ol>
  <li><p>What are the (updlock, holdlock, rowlock) use for? When I use these three same time, how does effect my query and transaction?</p></li>
  <li><p>Why did Transaction-2 block when it run the query? Because Both transaction selected different rows.</p></li>
  </ol>