CODEWITHSUNDEEP
mysqllockinginnodb
Joe Ijam
Joe Ijam

Reputation: 2179

Mysql lock read a single row for innodb

Need help to lock reading(select) row

At second 1 : Terminal A

START TRANSACTION;
SELECT * FROM tbl_processor WHERE id=1 FOR UPDATE; /*Or using this statement : SELECT * FROM tbl_processor WHERE id=1 LOCK IN READ MODE; */
UPDATE tbl_processor SET content='Updated content';  #Original content was content='Original content'   
SELECT SLEEP(10); #Just to sleep for testing purpose
COMMIT;

At second 2 : Terminal B;

SELECT * FROM tbl_processor WHERE id=1;
#This will return result immediately with content='Original content'
-----> This is my problem... I do not want this statement getting not updated content. It should wait until the Terminal A process completed

Upvotes: 2

Views: 4382

Answers (1)

spencer7593
spencer7593

Reputation: 108400

Change the second transaction (terminal B) to obtain a share lock:

SELECT * FROM tbl_processor WHERE id=1 LOCK IN SHARE MODE;

I suggest you review the relevant information in the MySQL Reference Manual here:

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

Upvotes: 2

Related Questions