title: What happens in MySQL when a locked row, which multiple threads are waiting on, gets deleted?
body: <p>I have a requirement where a request has to gain access to the top most row in the DB, read it and delete it, all in one go by locking the particular row.</p>
  <p>The use-case here is that a URL shortening application is going to query this table to get a pre-generated URL key and delete it once done.</p>
  <p>I am using Spring Data JPA, so I will add the following code</p>
  <p><strong>URLKeyEntity.java</strong></p>
  <pre><code>@Entity(name = &quot;urlKeyEntity&quot;)
  @Table(name = &quot;url_key&quot;)
  @Data
  public class URLKeyEntity {
  
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private Long id;
   private String urlKey;
  }
  </code></pre>
  <p><strong>URLKeyRepository.java</strong></p>
  <pre><code>@Repository
  public interface URLKeyRepository extends JpaRepository&lt;URLKeyEntity, Long&gt; {
  
   @Lock(LockMode.PESSIMISTIC_WRITE)
   @Query(&quot;SELECT u FROM urlKeyEntity u ORDER BY u.id&quot;)
   List&lt;URLKeyEntity&gt; fetchTopRow(Pageable p);
  }
  </code></pre>
  <p><strong>URLKeyService.java</strong></p>
  <pre><code>@Service
  public class URLKeyService {
  
   @Autowired
   private URLKeyRepository urlKeyRepository;
  
   @Transactional
   public String getUrlKey() {
    final URLKeyEntity u = urlKeyRepository.findTopRow(PageRequest.of(0,1));
    final String key = u.get(0).getUrlKey();
    urlKeyRepository.delete(u.getId());
   }
  }
  </code></pre>
  <p>What I am having difficulty understanding is how this will play out when multiple threads are trying to execute the same method.</p>
  <p>Say, threads <strong>T1</strong> and <strong>T2</strong> are executing the same method <strong>parallelly</strong>. At any given point, T1 acquires the lock on the first row R1 while T2 waits for the lock on the same row.</p>
  <p>By the time T1 is done, the row itself does not exist, how will T2 then proceed?</p>
  <p>And if we acquire a lock on the entire table, then that may result in poor performance as the write load increases.</p>
  <p>What is the best way to handle this so that locking does not become a bottle-neck in scalability?</p>