mysql lock mode s,如何锁（S，X，IS，IX）在Mysql中与查询如UP UPDATE / LOCK IN SHARE MODE？...

1:

I was trying this and it was working 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 IS lock and lock the selected rows with S lock.

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?

2:

//session1

start transaction;

select * from orders where id = 21548 FOR UPDATE;

Keep this session1 same and try this in the 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 into IX mode and selected row into X mode.

As X mode is incompatible with S mode then how come select query in second session is getting executed?

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.

I have read a lot of stuff regarding this but not able to clear my doubts.Please help.

解决方案

Bill Karwin answered this question through email.He said:

The same transaction that holds an S lock can promote the lock to an X lock. This is not a conflict.

The SELECT in session 1 with FOR UPDATE acquires an X lock. A simple SELECT query with no locking clause specified does not need to acquire an S lock.

Any UPDATE or DELETE needs to acquire an X lock. That's implicit. Those statements don't have any special locking clause for that.

For more details on IS/IX locks and FOR UPDATE/LOCK IN SHARE MODE please visit

shared-and-exclusive-locks .