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.
Add a comment
|
1 Answer
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)
1 Comment
Rohit
As per two phase locking, the outside sql statements cannot access the data modified by the intermediate statements of the transaction until the transaction gets committed, but there is no blocking of outside sql statements although some transactions are in the middle. How it’s happening? Does two phase locking really exist? The DB Engine I am using is INNODB