InnoDB Storage Engine — Concept Check
Questions on InnoDB Storage Engine. InnoDB is MySQL's default ACID-compliant engine providing row-level locking, MVCC, foreign keys, crash recovery via redo/undo logs, and clustered primary key index.
Questions in this quiz
8 questions on InnoDB Storage Engine — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A database server is experiencing high disk I/O and low buffer pool hit ratio. What should be done to improve performance?
- ADecrease the innodb_buffer_pool_size
- BSwitch to a table-level locking mechanism
- CDisable MVCC
- DIncrease the innodb_buffer_pool_size
2.A query on the 'orders' table using a secondary index is slow. What is a likely reason?
- AInnoDB does not support secondary indexes
- BSecondary index lookups require two B-Tree traversals
- CSecondary indexes store full row data
- DThe buffer pool is too large
3.Which scenario can lead to a deadlock in InnoDB?
- ATwo transactions lock rows in different orders
- BA single transaction locks rows sequentially
- CUsing AUTO_INCREMENT for primary keys
- DSetting a large buffer pool size
4.Why might using UUIDs as primary keys in InnoDB be problematic?
- AThey cause random B-Tree page splits
- BThey reduce the size of secondary indexes
- CThey improve query performance
- DThey ensure sequential data insertion
5.What happens when a transaction reads a row with a shared lock in InnoDB?
- AOther transactions can write but not read the row
- BOther transactions can read but not write the row
- COther transactions cannot read or write the row
- DOther transactions can both read and write the row
6.A developer believes that increasing the buffer pool size will always improve performance. What is a misunderstanding here?
- AA buffer pool too large can lead to OS swapping
- BA buffer pool size is irrelevant to performance
- CBuffer pool size is fixed and cannot be changed
- DBuffer pool size affects only write operations
7.What is a key advantage of using a clustered primary key index in InnoDB?
- AIt prevents all page fragmentation
- BIt stores only the primary key value
- CPrimary key lookups require only one B-Tree traversal
- DSecondary indexes are not needed
8.A transaction is using MVCC to read data. What does this mean for concurrent write operations?
- AReads block all concurrent writes
- BWrites are converted to reads
- CReads and writes are blocked
- DWrites are not blocked by reads