MySQL Database Multiple Choice Questions on “Scheduling and Locking Issues”.
1. Which of these has higher priority?
writes, reads
a) writes
b) reads
c) same priority
d) machine dependent
Answer: a
Clarification: A client performing an operation that modifies a table is a writer and the client that performs a retrieval from the table is a reader. The writes have higher priority than reads.
2. How many of the following implement the default scheduling property of MySQL?
MyISAM, MERGE, MEMORY
a) 0
b) 1
c) 2
d) 3
Answer: d
Clarification: The MyISAM, MERGE and the MEMORY storage engines implement the default scheduling policy of MySQL with the help of the table locks. Whenever a client accesses a table a lock for it must be acquired first.
3. Which statement issues a lock on tables?
a) ISSUE LOCK
b) LOCK ISSUE
c) LOCK TABLES
d) ISSUE LOCKS
Answer: c
Clarification: Locks can be acquired and released explicitly by issuing LOCK TABLES and UNLOCK TABLES statements. Normally the server’s lock manager automatically acquires locks as necessary and releases them when they no longer are needed.
4. Reading from a table does not change it.
a) True
b) False
Answer: a
Clarification: Reading does not change the table. There is no reason one reader should prevent another from accessing the table. So a read lock enables other clients to read the table at the same time.
5. To how many of these does the LOW_PRIORITY modifier apply to?
DELETE, INSERT, LOAD DATA, REPLACE
a) 1
b) 2
c) 3
d) 4
Answer: d
Clarification: The LOW_PRIORITY statement modifier influences the default scheduling policy of MySQL. This keyword applies to all the four: DELETE, INSERT, LOAD DATA, REPLACE. It also applies to UPDATE.
6. The number of tables among the following for which HIGH_PRIORITY have an effect are _________________
MyISAM, MEMORY, MERGE
a) 0
b) 1
c) 2
d) 3
Answer: d
Clarification: In MySQL, the LOW_PRIORITY and HIGH_PRIORITY modifiers have an effect only for those storage engines that use table locks. The storage engines MyISAM, MERGE and MEMORY use table locks.
7. The number of tables for which DELAYED works is _________________
MyISAM, MEMORY, ARCHIVE, BLACKHOLE
a) 1
b) 2
c) 3
d) 4
Answer: d
Clarification: The ‘DELAYED’ statement modifier that influences the default scheduling policies of MySQL works for the following tables: MyISAM, MEMORY, ARCHIVE and as of MySQL 5.1.19, also for BLACKHOLE tables.
8. Find the odd one out in terms of scheduling policies.
a) MyISAM
b) InnoDB
c) MEMORY
d) MERGE
Answer: b
Clarification: The InnoDB storage engine implements locking at a different level. It has differing performance characteristics in terms of contention management. It uses row-level locks.
9. The use of table level locks is always useful in MyISAM tables.
a) True
b) False
Answer: b
Clarification: MyISAM is extremely fast for retrievals but the use of table level locks can be a problem in environments with mixed retrievals and updates if the retrievals are long running.
10. Which table is preferred when there are many updates?
a) MyISAM
b) InnoDB
c) MEMORY
d) MERGE
Answer: b
Clarification: The InnoDB tables can provide better performance when there are many updates. Locking is done at the row level rather than at the table level. So the extent of the table that is locked is smaller.