title: How locks (S,X,IS,IX) work in Mysql with queries like FOR UPDATE/LOCK IN SHARE MODE?
body: <p><strong>1:</strong> </p>
  
  <p>I was trying this and it was working fine:</p>
  
  <pre><code>start transaction; 
   select * from orders where id = 21548 LOCK IN SHARE MODE;
   update orders set amount = 1500 where id = 21548;
  commit;
  </code></pre>
  
  <p>According to the definition of <strong>LOCK IN SHARE MODE</strong> , it locks the table with IS lock and lock the selected rows with S lock.</p>
  
  <p>When a row is locked with S lock.How can it be modified without releasing lock?
  It needs X lock to modify it.Right? Or is it valid only for different connection transaction?</p>
  
  <p><strong>2:</strong> </p>
  
  <pre><code>//session1
  start transaction;
  select * from orders where id = 21548 FOR UPDATE;
  </code></pre>
  
  <p>Keep this session1 same and try this in the different session:</p>
  
  <pre><code> //session2
   select * from orders where id = 21548; //working
   update orders set amount = 2000 where id = 21548; //waiting
  </code></pre>
  
  <p><strong>FOR UPDATE</strong> locks the entire table into IX mode and selected row into X mode.</p>
  
  <p>As X mode is incompatible with S mode then how come select query in second session is getting executed?</p>
  
  <p>One answer might be that select query is not asking for S lock that's why it's running successfully.But update query in the second session is also not asking for X lock , but as you execute it , it starts waiting for the lock held by session1.</p>
  
  <p>I have read a lot of stuff regarding this but not able to clear my doubts.Please help.</p>