history_1索引 mysql_高性能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 israrely used in practice, because its performance isn’t much better than the otherlevels, which have many advantages. Reading uncommitted data is also known asa dirty read.

(2)READ COMMITTED

The default isolation level for most database systems (but not MySQL!) is READCOMMITTED . It satisfies the simple definition of isolation used earlier: a transactionwill see only those changes made by transactions that were already committedwhen 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 canrun the same statement twice and see different data.

(3)REPEATABLE READ

REPEATABLE READ solves the problems that READ UNCOMMITTED allows. It guaranteesthat any rows a transaction reads will “look the same” in subsequent reads withinthe same transaction, but in theory it still allows another tricky problem: phantomreads. 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 samerange again; you will then see the new “phantom” row. InnoDB and XtraDB solvethe 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 byforcing 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 andlock contention can occur. We’ve rarely seen people use this isolation level, butyour application’s needs might force you to accept the decreased concurrency infavor of the data stability that results.

3.为什么会产生死锁？

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

1 Transaction #1

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

7 START TRANSACTION;8 UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';9 UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';10 COMMIT;

4.命令

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

5 --These locking hints are frequently abused and should usually be avoided

6 SELECT ... LOCK INSHARE MODE7 SELECT ... FOR UPDATE

8

9 LOCK TABLES10 UNLOCK TABLES11

12 SHOW TABLE STATUS LIKE 'user'\G13

14 ALTER TABLE mytable ENGINE =InnoDB;15

16 mysql> CREATE TABLE innodb_table LIKEmyisam_table;17 mysql> ALTER TABLE innodb_table ENGINE=InnoDB;18 mysql> INSERT INTO innodb_table SELECT * FROMmyisam_table;19

20 mysql> START TRANSACTION;21 mysql> INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x ANDy;22 mysql> COMMIT;