Mysql InnoDB row lock experience

Source: Internet
Author: User


Experience of mysql InnoDB row lock TABLE structure: SQL code CREATE TABLE '1970 _ t' ('id' int (11) NOT NULL AUTO_INCREMENT, 'A' int (11) NOT NULL, 'B' int (11) NOT NULL, PRIMARY KEY ('id'), KEY '2017 _ t_a' ('A ')) ENGINE = InnoDB www.2cto.com now only has an index on column a and execute the SQL code update 20121015_t set B = 4 where a = 1 and B = 3 on two clients respectively; SQL code update 20121015_t set B = 2 where a = 1 and B = 1; the second www.2cto.com will wait for the timeout [Err] 1205-Lock wait timeout exceeded; try restarting transaction InnoDB does not lock the last row to be updated, but all rows scanned. that is to say, if you execute update 20121015_t set B = 2 where B = 1, all the rows in the table are locked.
Their official documents describe A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. it does not matter whether there are WHERE conditions in the statement that wowould exclude the row. innoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.