高性能MySQL笔记-第1章MySQL Architecture and History-001

1.MySQL架构图

 

2.事务的隔离性

事务的隔离性是specific rules for which changes are and aren’t visible inside and outside a transaction

(1)READ UNCOMMITTED

In the READ UNCOMMITTED isolation level, transactions can view the results of uncommitted transactions. At this level, many problems can occur unless you really,really know what you are doing and have a good reason for doing it. This level is rarely used in practice, because its performance isn’t much better than the other levels, which have many advantages. Reading uncommitted data is also known as a dirty read.

(2)READ COMMITTED

The default isolation level for most database systems (but not MySQL!) is READ COMMITTED . It satisfies the simple definition of isolation used earlier: a transaction will see only those changes made by transactions that were already committed when it began, and its changes won’t be visible to others until it has committed.This level still allows what’s known as a nonrepeatable read. This means you can run the same statement twice and see different data.

(3)REPEATABLE READ

REPEATABLE READ solves the problems that READ UNCOMMITTED allows. It guarantees that any rows a transaction reads will “look the same” in subsequent reads within the same transaction, but in theory it still allows another tricky problem: phantom reads. Simply put, a phantom read can happen when you select some range of rows,another transaction inserts a new row into the range, and then you select the same range again; you will then see the new “phantom” row. InnoDB and XtraDB solve the phantom read problem with multiversion concurrency control, which we explain later in this chapter.

REPEATABLE READ is MySQL’s default transaction isolation level.

(4)SERIALIZABLE

The highest level of isolation, SERIALIZABLE , solves the phantom read problem by forcing transactions to be ordered so that they can’t possibly conflict. In a nutshell,SERIALIZABLE places a lock on every row it reads. At this level, a lot of timeouts and lock contention can occur. We’ve rarely seen people use this isolation level, but your application’s needs might force you to accept the decreased concurrency in favor of the data stability that results.

 

3.为什么会产生死锁？

当不同的事务请求同一资源，发生循环引用时，就会产生死锁。A deadlock is when two or more transactions are mutually holding and requesting locks on the same resources, creating a cycle of dependencies. Deadlocks occur when transactions try to lock resources in a different order.

Transaction #1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;
Transaction #2
START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

 

 

4.命令

SHOW VARIABLES LIKE 'AUTOCOMMIT';
SET AUTOCOMMIT = 1;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- These locking hints are frequently abused and should usually be avoided
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

LOCK TABLES  
UNLOCK TABLES

SHOW TABLE STATUS LIKE 'user' \G

ALTER TABLE mytable ENGINE = InnoDB;

mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;

mysql> START TRANSACTION;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
mysql> COMMIT;