title: lock wait timout in InnoDb
body: <p>I have created a mysql table with (date,name,id,money_spent)columns having (date,name,id) as the primary key (since this combination is always unique) and InnoDb is the storage engine .</p>
  <p>date - Date datatype,
  name - varchar(30),
  id - varchar(100),</p>
  <p>I have a logic where in multiple update queries are fired in parallel . For Eg (considering these 2 for now )</p>
  <p>T1 :</p>
  <pre><code> update test_database.users set money_spend = 1000
       where date between &quot;2020-01-01&quot; and &quot;2020-01-10&quot;
         and name = &quot;John&quot;;
  </code></pre>
  <p>T2 :</p>
  <pre><code>update test_database.users set money_spend = 1005
       where date between &quot;2020-01-01&quot; and &quot;2020-01-10&quot;
         and name = &quot;Tammy&quot;;
  </code></pre>
  <p>Running these 2 Transaction in parallel causes T2 to timeout because of the lock acquired by T1.</p>
  <p>transaction isolation level - repeatable read isolation level</p>
  <p>InnoDb Engine Status</p>
  <pre><code>
  LOCK WAIT 19543 lock struct(s), heap size 376, 1407774 row lock(s)
  MySQL thread id 105402, OS thread handle 0x2ba11d68a700, query id 16862373 10.11.3.212 connect-dev Sending data
  update test_database.users set money_spend = 1005 where date between &quot;2020-01-01&quot; and &quot;2020-01-10&quot; and name = &quot;Tammy&quot;;
  ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 154606 page no 243093 n bits 78 index `PRIMARY` of table `test_database`.`users` trx id 44023639357 lock_mode X waiting
  Record lock, heap no 78 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
   0: len=3; bufptr=0x2b9fea4e2d5e; hex= 8fc95b; asc   [;;
   1: len=8; bufptr=0x2b9fea4e2d61; hex= 4170706e65787573; asc John;;
   2: len=8; bufptr=0x2b9fea4e2d69; hex= 3230393835373832; asc 20985782;;
   3: len=6; bufptr=0x2b9fea4e2d71; hex= 000a3f47ff22; asc   ?G &quot;;;
   4: len=7; bufptr=0x2b9fea4e2d77; hex= 0f011040350d5b; asc    @5 [;;
  </code></pre>
  <p>Looking at the innoDb status it looks like T2 is trying to acquire a lock on the record having &quot;John&quot; as the name but since records with &quot;John&quot; as the name are already locked by T1 , T2 times out .</p>
  <p>Question : Since InnoDb supports row level locking shouldn't transaction(T1) acquire a lock on only the records having date between &quot;2020-01-01&quot; and &quot;2020-01-10&quot; and name as &quot;John&quot;.Why is it giving a time out for the T2 transaction since the name part of the index is different (T2 is having &quot;Tammy&quot; as the name)</p>