As we all know, row(record) lock are supported in Innodb engine. There is no doubt that the following sql is atomic and in a transaction.
update tableA t
set t.oneField = someValue
where t.primaryKey = id
But I am confused at the following situation(without where condition)
update tableA t
set t.oneField = someValue
What I want to ask is, will mysql lock the entire table while executing this sql? More specific, when rowA has been processed and mysql is processing other rows, is rowA still locked while this sql is still processing? Any tool or sql command could use to know whether rowB is locked or not?
I have read doc https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html but still confused.
More over, if it is possible, could you give me a specific case or demo or experiment to illustrate your conclusion. Something that I could do it myself both ok.
Thanks a lot.
Saying that does not "lock the table" is being technically correct. In reality, it "locks every row". To the user these feel the same.
Both of your two queries will try to run, but they will soon interfere with each other. Probably one will delay until the other finishes. In some situations, a "deadlock" will be detected and one query will be aborted. (You must test for errors.)
The important distinction with the predecessor (ENGINE=MyISAM) is that all operations "locked the table". Updating one row could not start until a separate thread, which was updating a different row, finished. And, the interference between UPDATE and SELECT was greater.
With InnoDB, updates of separate rows can happen simultaneously, and selects can also be going on while the updates are happening.