3.1-數(shù)據(jù)庫事務(wù)

什么是事務(wù)

一系列有序的數(shù)據(jù)庫操作：

要么全部成功

要么全部回退到操作前的狀態(tài)

中間狀態(tài)對其他連接不可見

事務(wù)的基本操作：

基本操作 說明

start transaction 開始事務(wù)

commit 提交(全部完成)

rollback 回滾(回到初始狀態(tài))

-- 開啟一個事務(wù)

start transaction;

-- 或者使用(非標(biāo)準(zhǔn)sql)

begin;

insert into t values (1, 1, 1);

-- 事務(wù)結(jié)束，插入成功

commit;

begin;

insert into t values (2, 1, 1);

insert into t values (3, 1, 1);

insert into t values (4, 1, 1);

-- 事務(wù)結(jié)束，沒有插入數(shù)據(jù)

rollback;

begin;

insert into t values (1, 1, 1);

savepoint a1;

insert into t values (2, 1, 1);

-- 回滾到指定的保存點

rollback to a1;

commit;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

自動提交

autocommit可以在session級別設(shè)置

每個DML操作都自動提交

DDL永遠(yuǎn)都是自動提交，無法通過rollback回滾

事務(wù)的四個基本屬性(ACID)

原子性(Atomicity)

一致性(Consistency)

隔離性(Isolation)

持久性(Durability)

事務(wù)的原子性

包含在事務(wù)中的操作要么全部被執(zhí)行，要么都不執(zhí)行

中途數(shù)據(jù)庫或應(yīng)用發(fā)生異常，未提交的事務(wù)都應(yīng)該被回滾

事務(wù)的一致性

數(shù)據(jù)的正確性，合理性，完整性

數(shù)據(jù)一致性應(yīng)該符合應(yīng)用需要規(guī)則：

余額不能是負(fù)數(shù)

交易對象必須先有賬號

用戶賬號不能重復(fù)

事務(wù)的結(jié)果需要滿足數(shù)據(jù)的一致性約束

事物的持久性

提交完成的事務(wù)對數(shù)據(jù)庫的影響必須是永久性的

數(shù)據(jù)庫異常不會丟失事務(wù)更新

通常認(rèn)為成功寫入磁盤的數(shù)據(jù)即為持久化成功

事務(wù)的持久化的實現(xiàn)

數(shù)據(jù)文件持久化

隨機同步刷新(慢)

事務(wù)日志持久化與實例恢復(fù)

順序同步刷新(快) -> 事務(wù)日志

隨機異步刷新 -> 磁盤

事務(wù)日志 -> 磁盤(實例恢復(fù))

事務(wù)的隔離性

數(shù)據(jù)庫事務(wù)在提交完成前，中間的任何數(shù)據(jù)變化對其他的事務(wù)都是不可見的。

數(shù)據(jù)庫隔離現(xiàn)象

隔離現(xiàn)象 描述

臟讀(Dirty Read) 事務(wù)B讀到事務(wù)A尚未提交的數(shù)據(jù)變更

不可重復(fù)讀(NonRepeatable Read) 事務(wù)B讀取前后兩次讀取一條記錄之間該記錄被事務(wù)A修改并提交，于是事務(wù)B讀到了不一樣的結(jié)果

幻讀(Phantom Read) 事務(wù)B按條件匹配到了若干行記錄并修改。但是由于修改過程中事務(wù)A新插入了符合條件記錄，導(dǎo)致B更新完成后發(fā)現(xiàn)仍有符合條件卻未被更新的記錄。

數(shù)據(jù)庫隔離等級

隔離等級 臟讀 不可重復(fù)讀 幻讀

未提交讀 可能 可能 可能

已提交讀 不可能 可能 可能

可重復(fù)讀 不可能 不可能 可能

可串行化讀 不可能 不可能 不可能

MySQL的事務(wù)隔離級別

InnoDB默認(rèn)標(biāo)記為可重復(fù)讀

InnoDB并不是標(biāo)準(zhǔn)定義上的課重復(fù)讀

InnoDB默認(rèn)在可重復(fù)讀的基礎(chǔ)上避免幻讀

MySQL事務(wù)隔離級別設(shè)置

可在global/session/下個事務(wù)，級別分別進行設(shè)置

建議使用Read committed(同Oracle)

或者建議使用默認(rèn)的Repeatable read

set tx_isolation = ''

-- 設(shè)置隔離級別

1

2

事務(wù)與并發(fā)寫

某個正在更新的記錄再提交或回滾前不能被其他事務(wù)同時更新

事務(wù)回滾的實現(xiàn)

回滾段(rollback segment)與數(shù)據(jù)前像

3.2-存儲引擎概述

MySQL程序?qū)哟渭軜?gòu)

MySQL架構(gòu)

MySQL存儲引擎

有多種可選方案，可插拔，可修改存儲引擎

基于表選擇使用何種存儲引擎

主要存儲引擎

存儲引擎 常用度 支持事務(wù)

InnoDB 主要，推薦 是

MyISAM 古老，偶爾有用，系統(tǒng)表 否

MEMORY 偶爾臨時表有用，純內(nèi)存 否

BLACKHOLE 不用來存放數(shù)據(jù)，個別特殊用處 否

TokuDB 新穎，個別特殊場景有奇效 是

Cluster 新穎，分布式，內(nèi)存，線上不要用 是

InnoDB存儲引擎

索引組織表

支持事務(wù)

支持行級鎖

數(shù)據(jù)塊緩存

日志持久化

穩(wěn)定可靠，性能好，線上盡量使用InnoDB

MyISAM存儲引擎

堆表

不支持事務(wù)

只維護索引緩存池，表數(shù)據(jù)緩存交給操作系統(tǒng)

鎖粒度較大

數(shù)據(jù)文件可以直接拷貝，偶爾可能會用上

不建議線上業(yè)務(wù)數(shù)據(jù)使用

MWMORY存儲引擎

數(shù)據(jù)全內(nèi)存存放，無法持久化

性能較高

不支持事務(wù)

適合偶爾作為臨時表使用

create temporary table tmp (id int) engine = memory ;

BLACKHOLE存儲引擎

數(shù)據(jù)不作任何存儲

利用MySQL Replicate，充當(dāng)日志服務(wù)器

在MySQL Replicate環(huán)境中充當(dāng)代理主

TokuDB

分形樹存儲結(jié)構(gòu)

支持事務(wù)

行鎖

壓縮效率較高

適合大批量insert的場景

MySQL Cluster

多主分布式集群

數(shù)據(jù)節(jié)點間冗余，高可用

支持事務(wù)

設(shè)計上易于擴展

面向未來，線上慎用

改變表的存儲引擎

alter table m ENGINE=innodb;

3.3-InnoDB存儲引擎

InnoDB存儲引擎體系架構(gòu)

InnoDB

InnoDB相關(guān)的磁盤文件

文件 名稱 數(shù)量 位置

系統(tǒng)表空間 ibdata1 一個實例一個 innodb_data_home_dir

日志文件 ib_logfile0/1 一個實例兩個(可配置) innodb_log_group_home_dir

表定義文件 表名.frm 每張表一個 Schema目錄下

表數(shù)據(jù)文件 表名.ibd 如果innodb_file_per_table = 1, 則每張表一個 Schema目錄下

InnoDB系統(tǒng)表空間文件

ibdata1里存放了什么:

回滾段

所有InnoDB表元數(shù)據(jù)信息

Double Write, Insert buffer dump等等….

自動擴展機制

InnoDB與磁盤文件有關(guān)的參數(shù)

參數(shù) 樣例值 備注

innodb_data_home_dir /data/mysql/node_1 數(shù)據(jù)主目錄

innodb_log_group_home_dir /data/mysql/node_1 一般同上

innodb_data_file_path ibdata1:512M:autoextned 請開啟autoextned

innodb_autoextend_increment 128 MB,勿太大或太小

innodb_file_per_table 1 強烈建議開啟

innodb_log_file_size 100MB 性能相關(guān)

innodb_log_files_in_group 2 性能相關(guān)

InnoDB數(shù)據(jù)文件存儲結(jié)構(gòu)

索引組織表(聚簇表)

根據(jù)表邏輯主鍵排序

數(shù)據(jù)節(jié)點每頁16K

根據(jù)主鍵尋址速度很快

主鍵值遞增的insert插入效率較好

主鍵值隨機insert插入效率差

因此，InnoDB表必須指定主鍵，建議使用自增數(shù)字

InnoDB數(shù)據(jù)塊緩存池

數(shù)據(jù)的讀寫需要經(jīng)過緩存

數(shù)據(jù)以整頁(16K)為單位讀取到緩存中

緩存中的數(shù)據(jù)以LRU策略換出

IO效率高，性能好

InnoDB Buffer Pool相關(guān)參數(shù)

參數(shù) 樣例值 備注

innodb_buffer_pool_size 10G 根據(jù)總物理內(nèi)存設(shè)置

InnoDB數(shù)據(jù)持久化與事務(wù)日志

事務(wù)日志實時持久化

內(nèi)存變化數(shù)據(jù)(臟數(shù)據(jù))增量異步刷出到磁盤

實例故障靠重放日志恢復(fù)

性能好，可靠，恢復(fù)快

InnoDB日志持久化相關(guān)參數(shù)

參數(shù) 樣例值 備注

innodb_flush_log_at_trx_commit 1 可選：0：每隔1s寫入并持久化一次日志。1：每次commit都寫入并持久化日志。2：每次提交日志寫到內(nèi)存，每1s持久化一次

InnoDB行級鎖

寫不阻塞讀

不同行間的寫互相不阻塞

并發(fā)性能好

InnoDB與事務(wù)ACID

事務(wù)ACID特性完整支持

回滾段失敗回滾

支持主外鍵約束

事務(wù)版本+回滾段=MVCC

事務(wù)日志持久化

默認(rèn)可重復(fù)讀隔離級別，可以調(diào)整

3.4-InnoDB事務(wù)鎖

什么是計算機程序鎖

計算機程序鎖

控制對共享資源進行并發(fā)訪問

保護數(shù)據(jù)的完整性和一致性

數(shù)據(jù)庫中的鎖

分為兩個大類

lock latch/mutex

對象 事務(wù)

保護 數(shù)據(jù)庫邏輯內(nèi)容

持續(xù)時間 事務(wù)過程中

我們主要關(guān)心的是事務(wù)鎖

數(shù)據(jù)庫事務(wù)并發(fā)

對同一行記錄的修改必須串行化

事務(wù)鎖粒度

行鎖

InnoDB, Oracle

頁鎖

SQL Server

表鎖

MyISAM, Memory

鎖升級

InnoDB存儲引擎中的鎖模式與粒度

四種基本鎖模式

共享鎖(S) - 讀鎖 - 行鎖

排他鎖(X) - 寫鎖 - 行鎖

意向共享鎖(IS) - 表級

意向排他鎖(IX) - 表級

意向鎖

意向鎖總是自動先加，并且意向鎖自動加自動釋放

意向鎖提示數(shù)據(jù)庫這個session將要在接下來施加何種鎖

意向鎖和X/S鎖級別不同，除了阻塞全表級別的X/S鎖外其他任何鎖

InnoDB鎖模式互斥

鎖互斥

數(shù)據(jù)庫加鎖操作

一般的select語句不加任何鎖，也不會被任何事物鎖阻塞

讀的隔離性由MVCC確保

S鎖

手動：select from tb_test lock in share mode;

自動：insert前

X鎖

手動：select from tb_test lock for update;

自動：update，delete前

InnoDB行鎖的實現(xiàn)

通過索引項加鎖實現(xiàn)

只有條件走索引才能實現(xiàn)行級鎖

索引上有重復(fù)值，可能鎖住多個記錄

查詢有多個索引可以走，可以對不同索引加鎖

是否對索引加鎖實際上取決于MySQL執(zhí)行計劃

自增主鍵做條件更新，性能最好

沒有索引的話會對整張表加鎖。

InnoDB的gap lock

什么是幻讀

gap lock消滅幻讀

InnoDB消滅幻讀僅僅為了確保statement模式replicate的主從一致性

小心gap lock

自增主鍵做條件更新，性能最好

死鎖

什么是死鎖

A、B兩個事務(wù)，A先更新t1，同時B更新t2，A再更新t2，B再更新t1就發(fā)生了死鎖。

死鎖數(shù)據(jù)庫自動解決

數(shù)據(jù)庫挑選沖突事務(wù)中回滾代價較小的事務(wù)回滾

死鎖預(yù)防

單表死鎖可以根據(jù)批量更新里的更新條件排序

可能沖突的跨表事務(wù)盡量避免并發(fā)

盡量縮短事務(wù)長度

業(yè)務(wù)邏輯加鎖

業(yè)務(wù)流程中的悲觀鎖

任何的并發(fā)修改都有可能造成我們的業(yè)務(wù)邏輯最終的錯誤，在事務(wù)流程中一開始就加鎖，最后釋放

如何縮短鎖的時間

總結(jié)

以上是生活随笔為你收集整理的mysql支持事务的存储引擎_MySQL基础(三)【MySQL事务与存储引擎】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。