How do locks (S, X, IS, IX) work in Mysql with queries such as FOR UPDATE / LOCK IN SHARE MODE?

Question

1:

I tried to do this and it worked fine:

start transaction; select * from orders where id = 21548 LOCK IN SHARE MODE; update orders set amount = 1500 where id = 21548; commit; 

According to the definition of LOCK IN SHARE MODE, it locks the table with the IS lock and locks the selected rows with the S lock.

When a row is locked with lock S. How can I change it without releasing the lock? It requires an X lock to change it. Correct? Or is it valid only for different connection transactions?

2:

 //session1 start transaction; select * from orders where id = 21548 FOR UPDATE; 

Keep this session1 the same and try this in a different session:

  //session2 select * from orders where id = 21548; //working update orders set amount = 2000 where id = 21548; //waiting 

FOR UPDATE, locks the entire table in mode IX and the selected row in mode X.

Since mode X is incompatible with S-mode, then how is the selection request performed in the second session?

One answer may be that the select request does not request an S-lock, why it works successfully. But in the second session of the update request, the X lock is also not requested, but as it starts, it starts to wait for the lock on session1.

I read a lot of things regarding this, but could not clear my doubts. Please, help.

Answer 1

Bill Carwin answered this question by email. He said:

• The same transactions that contain lock S can help lock lock X. This is not a conflict.

• A SELECT in session 1 with FOR UPDATE receives an X lock. A simple SELECT query without a lock does not require an S lock.

Any UPDATE or DELETE must get an X lock. This is implicit. These operators do not have a special lock clause for this.

For more information on IS / IX and FOR UPDATE / LOCK IN SHARE MODE locks , please visit shared-and-exclusive-locks .