title: What locks following queries acquire in MySQL InnoDB
body: <p>I am trying to investigate deadlock issues in my application. My table looks something like this.</p>
  
  <pre><code>CREATE TABLE `requests` (
    `req_id` bigint(20) NOT NULL auto_increment,
    `status` varchar(255) default NULL,
    `process_id` varchar(200) default NULL,
    PRIMARY KEY  (`req_id`),
    KEY `status_idx` USING BTREE (`status`),
    KEY `pk_idx_requests` USING BTREE (`req_id`),
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <ul>
  <li>A Service(multiple threads) issues insert statements on this table.</li>
  <li><p>Multiple clients issue following queries  in order in two separate transactions.</p>
  
  <p>update requests set process_id='" + hostName + "' where status='Received' and process_id is null order by req_id asc limit 100"</p>
  
  <p>select * from requests where process_id='"+ hostName + "' where status='Received';</p>
  
  <p>update requests set status='Processing' where req_id='xyz'</p></li>
  </ul>
  
  <p>Req_id in 3rd query is list of req ids retrieved from 2nd query.</p>
  
  <p>But on client side some times, we see following exception.</p>
  
  <pre><code>Deadlock found when trying to get lock; try restarting transaction
  org.hibernate.exception.LockAcquisitionException: could not execute native bulk manipulation query
  </code></pre>
  
  <p>Can above queries result in deadlock, if yes how can we resolve it? Also is there a way to reproduce this issue locally?</p>
  
  <p>Here is output of 'show innodb status'</p>
  
  <pre><code>LATEST DETECTED DEADLOCK
  ------------------------
  120507  6:03:21
  *** (1) TRANSACTION:
  TRANSACTION 115627, ACTIVE 1 sec starting index read
  mysql tables in use 1, locked 1
  LOCK WAIT 3 lock struct(s), heap size 1248, 25 row lock(s)
  MySQL thread id 432399, OS thread handle 0x419e4940, query id 4111695 * * * Searching rows for update
  update requests set process_id='**' where status='Received' and process_id is null order by req_id asc limit 100
  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 4 page no 3797 n bits 136 index `PRIMARY` of table `db`.`requests` trx id 115627 lock_mode X locks rec but not gap waiting
  Record lock, heap no 67 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
  *** (2) TRANSACTION:
  TRANSACTION 115626, ACTIVE 1 sec updating or deleting
  mysql tables in use 1, locked 1
  3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
  MySQL thread id 432403, OS thread handle 0x41c19940, query id 4111694 * * *  Updating
  update requests set status='Processing', process_id='**' where req_id=3026296
  *** (2) HOLDS THE LOCK(S):
  RECORD LOCKS space id 4 page no 3797 n bits 136 index `PRIMARY` of table `db`.`requests` trx id 115626 lock_mode X locks rec but not gap
  Record lock, heap no 67 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
  </code></pre>