Sample questions
Mysql Innodb TransactionsDifficulty 1
What is the default transaction isolation level of the InnoDB storage engine in MySQL?
- aREAD UNCOMMITTED
- bREAD COMMITTED
- cREPEATABLE READ✓
- dSERIALIZABLE
Explanation:InnoDB's default isolation level is REPEATABLE READ. This differs from PostgreSQL's READ COMMITTED default. Under InnoDB REPEATABLE READ, repeated consistent non-locking reads use the snapshot established by the first such read (or by START TRANSACTION WITH CONSISTENT SNAPSHOT).
Mysql Innodb TransactionsDifficulty 2
A developer switches from PostgreSQL to MySQL and assumes both databases have the same default isolation behavior. What is the concrete difference they need to be aware of?
- aMySQL/InnoDB defaults to REPEATABLE READ, while PostgreSQL defaults to READ COMMITTED✓
- bMySQL/InnoDB defaults to READ UNCOMMITTED, while PostgreSQL defaults to REPEATABLE READ
- cBoth default to READ COMMITTED, but MySQL enforces it more strictly
- dBoth default to SERIALIZABLE, but MySQL allows disabling it per-session
Explanation:InnoDB's default isolation level is REPEATABLE READ, whereas PostgreSQL's default is READ COMMITTED. This means a repeated SELECT inside the same MySQL transaction sees the same snapshot even if other transactions commit changes meanwhile, unlike PostgreSQL's default where each statement sees the latest committed data.
Mysql Innodb TransactionsDifficulty 1
Which statement correctly starts an explicit transaction in MySQL?
- aBEGIN TRANSACTION WORK;
- bSTART TRANSACTION;✓
- cOPEN TRANSACTION;
- dTRANSACTION START;
Explanation:START TRANSACTION; (or the shorter BEGIN;) is the standard way to start an explicit transaction in MySQL. It temporarily disables autocommit for that transaction until COMMIT or ROLLBACK.
Mysql Innodb TransactionsDifficulty 2
By default, what is the value of the autocommit system variable in a new MySQL session, and what does it mean?
- aOFF — every statement must be explicitly wrapped in START TRANSACTION/COMMIT
- bOFF — statements are buffered until an explicit FLUSH is issued
- cON, but only applies to SELECT statements, not DML
- dON — each individual SQL statement is committed as its own transaction unless START TRANSACTION is used✓
Explanation:MySQL sessions default to autocommit = ON. This means each standalone statement (e.g. a single UPDATE) is automatically committed immediately after it runs, unless the session explicitly starts a multi-statement transaction with START TRANSACTION.
Mysql Innodb TransactionsDifficulty 2
What SQL statement should be used to undo all changes made within the current open transaction in InnoDB?
- aUNDO;
- bROLLBACK;✓
- cRESET TRANSACTION;
- dREVERT;
Explanation:ROLLBACK; undoes all changes made since the transaction began, using InnoDB's undo log to restore the previous row versions. COMMIT; is its counterpart, which makes the changes permanent.
Mysql Innodb TransactionsDifficulty 2
Which SQL clause acquires an exclusive row lock on the rows a SELECT reads, so other transactions cannot update or delete them until the current transaction ends?
- aSELECT ... FOR UPDATE✓
- bSELECT ... WITH LOCK
- cSELECT ... EXCLUSIVE
- dSELECT ... FOR EXCLUSIVE READ
Explanation:SELECT ... FOR UPDATE reads rows and takes an exclusive lock on them, blocking other transactions from updating, deleting, or (depending on isolation level) locking the same rows until the current transaction commits or rolls back.