Advanced Rails , Database Chapter memo(1)

AcitveRecord 支援的database

PostgreSQL
PostgreSQL’s support for concurrency is more mature than MySQL’s. Postgres supports
multiversion concurrency control (MVCC), which is even more advanced than
row-level locking.

One advantage that PostgreSQL may have in the enterprise is its similarity to commercial
enterprise databases such as Oracle, MS SQL Server, or DB2.

---

Multiversion Concurrency Control
MVCC gives each transaction a snapshot of the data it accesses, as the data existed at the start of the transaction. The transaction performs actions on the data, which are logged with timestamps

The primary advantage that MVCC has over locking is that MVCC does not block readers.

MVCC uses more storage space than locking because it has to store a snapshot for each in-progress transaction. And though MVCC never blocks reads, the DBMS may roll back update transactions if they cause a conflict.

---

MySQL
Use InnoDB for absolutely anything where data integrity or concurrency matter.
MyISAM, the default engine on most MySQL installations, does not support features
that most RDBMSs consider essential: foreign key constraints, row-level
locking, and transactions.

Unfortunately, InnoDB can be much slower than MyISAM, and the table sizes
are usually several times larger

if raw speed of reads or writes is the primary concern, MyISAM can
help. For example, a logging server for web analytics might use MyISAM tables:
you want to be able to dump logs into it as fast as possible, and reads are performed
far less often than writes.

Set the SQL mode to TRADITIONAL. This can be accomplished with the following
command:

SET GLOBAL sql_mode='TRADITIONAL';

This will make MySQL a little bit more strict, raising errors on incorrect data rather than silently discarding it.