Transactions in MySQL

Question

What Will MySQL uses for the transactions?MVCC(Multi Version Concurrency Control) or Row Level Locking.?
If both how can we shift from one to another.

Answer 1

This doesn't depend on MySQL itself, but on the used engine, e.g. InnoDB or MyIsam.

---

In the InnoDB transaction model, the goal is to combine the best properties of a multi-versioning database with traditional two-phase locking. InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle. The lock table in InnoDB is stored so space-efficiently that lock escalation is not needed: Typically, several users are permitted to lock every row in InnoDB tables, or any random subset of the rows, without causing InnoDB memory exhaustion.

(Source: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html)

---

MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, allowing only one session to update those tables at a time, making them more suitable for read-only, read-mostly, or single-user applications.

(Source: http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html)