What is true regarding InnoDB locking?
A.
InnoDB row locks may be escalated to page or table-level locks.
B.
InnoDB only uses row locks, not page or table-level locks,
C.
InnoDB uses row and table-level locks, but row locks are not escalates,
D.
InnoDB locks only those rows that are updated.
E.
InnoDB uses row-level or table-level locks depending on the number of rows affected.
Explanation:
Reference: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html
B
B
A
http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html
B
http://dev.mysql.com/doc/refman/5.6/en/table-locking.html
A
Meant to say A sorry.
After reading again I think it should be B.
InnoDB tables use row-level locking so that multiple sessions and applications can read from and write to the same table simultaneously, without making each other wait or producing inconsistent results. For this storage engine, avoid using the LOCK TABLES statement, because it does not offer any extra protection, but instead reduces concurrency. The automatic row-level locking makes these tables suitable for your busiest databases with your most important data, while also simplifying application logic since you do not need to lock and unlock tables. Consequently, the InnoDB storage engine is the default in MySQL 5.6.
MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB. The locking operations themselves do not have much overhead. But because only one session can write to a table at any one time, for best performance with these other storage engines, use them primarily for tables that are queried often and rarely inserted into or updated.
https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html
answer : C
I think C
C,D
A or B.. Tricky question
Ideally INNODB use row level locking.. So, i go with B.
Row-Level Locking
MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.
http://dev.mysql.com/doc/refman/5.7/en/internal-locking.html
D, thinking simple
I thinks correct answer is C .
A: false, innodb never escalates
B: false, DDL and AUTO_INCREMENT(*) use table-locks
C: true
D: false, you can SELECT FOR UPDATE without actually updating
E: false, innodb may lock multiple rows but never escalates
I agree with Mutex,
Intention Locks
InnoDB supports multiple granularity locking which permits coexistence of row-level locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table-level locks
and
AUTO-INC Locks
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case
At the beginning, the simplest answer seemed to me the B but then I had to turn onto the C answer.
+1
C