一、事務(wù)介紹

1.1 MySQL 事務(wù)的概念

事務(wù)是一種機(jī)制、一個(gè)操作序列，包含了一組數(shù)據(jù)庫(kù)操作命令（增刪改），并且把所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求，即這一組數(shù)據(jù)庫(kù)命令要么都執(zhí)行，要么都不執(zhí)行。

事務(wù)是一個(gè)不可分割的工作邏輯單元，在數(shù)據(jù)庫(kù)系統(tǒng)上執(zhí)行并發(fā)操作時(shí)，事務(wù)是最小的控制單元。事務(wù)是通過(guò)事務(wù)的整體性以保證數(shù)據(jù)的一致性。事務(wù)能夠提高在向表中更新和插入信息期間的可靠性。

事務(wù)適用于多用戶同時(shí)操作的數(shù)據(jù)庫(kù)系統(tǒng)的場(chǎng)景，如銀行、保險(xiǎn)公司及證券交易系統(tǒng)等等。

總的來(lái)說(shuō)所謂事務(wù)，它是一個(gè)操作序列，這些操作（增刪改）要么都執(zhí)行，要么都不執(zhí)行，它是一個(gè)不可分割的工作單位。

1.2 事務(wù)的ACID特點(diǎn)

ACID，是指在可靠數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）中，事務(wù)(transaction)應(yīng)該具有的四個(gè)特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。這是可靠數(shù)據(jù)庫(kù)所應(yīng)具備的幾個(gè)特性。

原子性

指事務(wù)是一個(gè)不可再分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生。

事務(wù)是一個(gè)完整的操作，事務(wù)的各元素是不可分的。事務(wù)中的所有元素必須作為一個(gè)整體提交或回滾。如果事務(wù)中的任何元素失敗，則整個(gè)事務(wù)將失敗。

一致性

指在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性約束沒(méi)有被破壞。

當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài)。在事務(wù)開(kāi)始前，數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)處于一致?tīng)顟B(tài)。在正在進(jìn)行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài)。當(dāng)事務(wù)成功完成時(shí)，數(shù)據(jù)必須再次回到已知的一致?tīng)顟B(tài)。

隔離性

指在并發(fā)環(huán)境中，當(dāng)不同的事務(wù)同時(shí)操縱相同的數(shù)據(jù)時(shí)，每個(gè)事務(wù)都有各自的完整數(shù)據(jù)空間。

對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)。修改數(shù)據(jù)的事務(wù)可在另一個(gè)使用相同數(shù)據(jù)的事務(wù)開(kāi)始之前訪問(wèn)這些數(shù)據(jù)，或者在另一個(gè)使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問(wèn)這些數(shù)據(jù)。

也就說(shuō)并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，一個(gè)用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫(kù)是獨(dú)立的。

持久性

在事務(wù)完成以后，該事務(wù)所對(duì)數(shù)據(jù)庫(kù)所作的更改便持久的保存在數(shù)據(jù)庫(kù)之中，并不會(huì)被回滾。

指不管系統(tǒng)是否發(fā)生故障，事務(wù)處理的結(jié)果都是永久的。一旦事務(wù)被提交，事務(wù)的效果會(huì)被永久地保留在數(shù)據(jù)庫(kù)中。

1.3 事務(wù)之間的相互影響

在多個(gè)事務(wù)并發(fā)操作同意給表數(shù)據(jù)時(shí)，不同的隔離級(jí)別可能會(huì)出現(xiàn)的一致性問(wèn)題：

• 臟讀：一個(gè)事務(wù)讀取了另一個(gè)事務(wù)未提交的數(shù)據(jù)，而這個(gè)數(shù)據(jù)是有可能回滾的。
• 不可重復(fù)讀：一個(gè)事務(wù)內(nèi)兩個(gè)相同的查詢卻返回了不同數(shù)據(jù)。這是由于查詢時(shí)系統(tǒng)中其他事務(wù)修改的提交而引起的。
• 幻讀：在一個(gè)事務(wù)里的兩次查詢會(huì)看到數(shù)據(jù)不一致的情況（可能是發(fā)現(xiàn)之前沒(méi)有的數(shù)據(jù)），這種情況可能因?yàn)閮纱尾樵冞^(guò)程中間有其它事務(wù)插入了新的數(shù)據(jù)并己提交。
• 丟失更新：一個(gè)事務(wù)修改數(shù)據(jù)并提交可能會(huì)覆蓋另一個(gè)事務(wù)修改和提交的數(shù)據(jù)。

隔離級(jí)別

1.4 設(shè)置隔離級(jí)別

事務(wù)隔離級(jí)別的作用范圍分為兩種：
● 全局級(jí)：對(duì)所有的會(huì)話有效
● 會(huì)話級(jí)：只對(duì)當(dāng)前的會(huì)話有效

查詢?nèi)质聞?wù)隔離級(jí)別：
show global variables like ‘%isolation%’;
SELECT @@global.tx_isolation;

查詢會(huì)話事務(wù)隔離級(jí)別：
show session variables like ‘%isolation%’;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

設(shè)置全局事務(wù)隔離級(jí)別：
set global transaction isolation level read committed;
set @@global.tx_isolation=‘read-committed’; #重啟服務(wù)后失效

設(shè)置會(huì)話事務(wù)隔離級(jí)別：
set session transaction isolation level repeatable read;
set @@session.tx_isolation=‘repeatable-read’;

1.5事務(wù)控制語(yǔ)句

BEGIN 或 START TRANSACTION：顯式地開(kāi)啟一個(gè)事務(wù)。
COMMIT 或 COMMIT WORK：提交事務(wù)，并使已對(duì)數(shù)據(jù)庫(kù)進(jìn)行的所有修改變?yōu)橛谰眯缘摹?br> ROLLBACK 或 ROLLBACK WORK：回滾會(huì)結(jié)束用戶的事務(wù)，并撤銷正在進(jìn)行的所有未提交的修改。
SAVEPOINT S1：使用 SAVEPOINT 允許在事務(wù)中創(chuàng)建一個(gè)回滾點(diǎn)，一個(gè)事務(wù)中可以有多個(gè) SAVEPOINT；“S1”代表回滾點(diǎn)名稱。
ROLLBACK TO [SAVEPOINT] S1：把事務(wù)回滾到標(biāo)記點(diǎn)。

實(shí)例：
1、測(cè)試提交事務(wù)

2、測(cè)試回滾事務(wù)

3、測(cè)試多點(diǎn)回滾

1.6使用 set 設(shè)置控制事務(wù)

SET AUTOCOMMIT=0;						#禁止自動(dòng)提交
SET AUTOCOMMIT=1;						#開(kāi)啟自動(dòng)提交，Mysql默認(rèn)為1
SHOW VARIABLES LIKE 'AUTOCOMMIT';		#查看Mysql中的AUTOCOMMIT值

如果沒(méi)有開(kāi)啟自動(dòng)提交，當(dāng)前會(huì)話連接的mysql的所有操作都會(huì)當(dāng)成一個(gè)事務(wù)直到你輸入rollback|commit;當(dāng)前事務(wù)才算結(jié)束。當(dāng)前事務(wù)結(jié)束前新的mysql連接時(shí)無(wú)法讀取到任何當(dāng)前會(huì)話的操作結(jié)果。
如果開(kāi)起了自動(dòng)提交，mysql會(huì)把每個(gè)sql語(yǔ)句當(dāng)成一個(gè)事務(wù)，然后自動(dòng)的commit。
當(dāng)然無(wú)論開(kāi)啟與否，begin; commit|rollback; 都是獨(dú)立的事務(wù)。

use kgc;
select * from account;
SET AUTOCOMMIT=0;
update account set money= money + 100 where name='B';
select * from account;
quit

mysql -u root -p
use kgc;
select * from account;						

二、存儲(chǔ)引擎介紹

MyISAM 表支持 3 種不同的存儲(chǔ)格式：
（1）靜態(tài)(固定長(zhǎng)度)表
靜態(tài)表是默認(rèn)的存儲(chǔ)格式。靜態(tài)表中的字段都是非可變字段，這樣每個(gè)記錄都是固定長(zhǎng)度的，這種存儲(chǔ)方式的優(yōu)點(diǎn)是存儲(chǔ)非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)；缺點(diǎn)是占用的空間通常比動(dòng)態(tài)表多。

（2）動(dòng)態(tài)表
動(dòng)態(tài)表包含可變字段，記錄不是固定長(zhǎng)度的，這樣存儲(chǔ)的優(yōu)點(diǎn)是占用空間較少，但是頻繁的更新、刪除記錄會(huì)產(chǎn)生碎片，需要定期執(zhí)行 OPTIMIZE TABLE 語(yǔ)句或 myisamchk -r 命令來(lái)改善性能，并且出現(xiàn)故障的時(shí)候恢復(fù)相對(duì)比較困難。

（3）壓縮表
壓縮表由 myisamchk 工具創(chuàng)建，占據(jù)非常小的空間，因?yàn)槊織l記錄都是被單獨(dú)壓縮的，所以只有非常小的訪問(wèn)開(kāi)支。

常用存儲(chǔ)引擎：InnoDB、MyISAM
MyISAM：不支持事務(wù)和外鍵約束，占用資源較小，訪問(wèn)速度快，表級(jí)鎖定，支持全文索引，適用于不需要事務(wù)處理，單獨(dú)寫(xiě)入或查詢的應(yīng)用場(chǎng)景。
InnoDB：支持事務(wù)處理、外鍵約束，緩存能力較好，支持行級(jí)鎖定，讀寫(xiě)并發(fā)能力較好，5.5版本后支持全文索引，適用于一致性要求高、數(shù)據(jù)更新頻繁的應(yīng)用場(chǎng)景。

2.1查看系統(tǒng)支持的存儲(chǔ)引擎

show engines;

#查看表使用的存儲(chǔ)引擎
方法一：
show table status from 庫(kù)名 where name=‘表名’\G

方法二：
use 庫(kù)名;
show create table 表名;

2.2 修改存儲(chǔ)引擎

1．通過(guò) alter table 修改
use 庫(kù)名;
alter table 表名 engine=MyISAM;

2．通過(guò)修改 /etc/my.cnf 配置文件，指定默認(rèn)存儲(chǔ)引擎并重啟服務(wù)
vim /etc/my.cnf
…
[mysqld]
…
default-storage-engine=INNODB

systemctl restart mysql.service
注意：此方法只對(duì)修改了配置文件并重啟mysql服務(wù)后新創(chuàng)建的表有效，已經(jīng)存在的表不會(huì)有變更。

3．通過(guò) create table 創(chuàng)建表時(shí)指定存儲(chǔ)引擎
use 庫(kù)名;
create table 表名(字段1 數(shù)據(jù)類型,…) engine=MyISAM;

2.3InnoDB行鎖與索引的關(guān)系

InnoDB行鎖是通過(guò)給索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，如果沒(méi)有索引，InnoDB將通過(guò)隱藏的聚簇索引來(lái)對(duì)記錄加鎖。

1）
delete from t1 where id=1;
如果id字段是主鍵，innodb對(duì)于主鍵使用了聚簇索引，會(huì)直接鎖住整行記錄。

2）
delete from t1 where name=‘a(chǎn)aa’;
如果name字段是普通索引，會(huì)先鎖住索引的兩行，接著會(huì)鎖住相應(yīng)主鍵對(duì)應(yīng)的記錄。

3）
delete from t1 where age=23;
如果age字段沒(méi)有索引，會(huì)使用全表掃描過(guò)濾，這時(shí)表上的各個(gè)記錄都將加上鎖。

//死鎖
死鎖一般是事務(wù)相互等待對(duì)方資源，最后形成環(huán)路造成的。

實(shí)例：

打開(kāi)兩個(gè)虛擬機(jī)

虛擬機(jī)一：

虛擬機(jī)二：

for update 可以為數(shù)據(jù)庫(kù)中的行上一個(gè)排它鎖。當(dāng)一個(gè)事務(wù)的操作未完成時(shí)候，其他事務(wù)可以讀取但是不能寫(xiě)入或更新。
#共享鎖：又叫做讀鎖，當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí)，對(duì)數(shù)據(jù)加上共享鎖，共享鎖可以同時(shí)加上多個(gè)。
#排他鎖：又叫做寫(xiě)鎖，當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫(xiě)入時(shí)，對(duì)數(shù)據(jù)加上排他鎖，排他鎖只可以加一個(gè)，它和其它的排他鎖,共享鎖都相斥。

//如何盡可能避免死鎖？
1）使用更合理的業(yè)務(wù)邏輯，以固定的順序訪問(wèn)表和行。
2）大事務(wù)拆小。大事務(wù)更傾向于死鎖，如果業(yè)務(wù)允許，將大事務(wù)拆小。
3）在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。
4）降低隔離級(jí)別。如果業(yè)務(wù)允許，將隔離級(jí)別調(diào)低也是較好的選擇，比如將隔離級(jí)別從RR調(diào)整為RC，可以避免掉很多因?yàn)間ap鎖造成的死鎖。
5）為表添加合理的索引。如果不使用索引將會(huì)為表的每一行記錄添加上鎖，死鎖的概率大大增大。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-496766.html

到了這里，關(guān)于MySQL----事物與存儲(chǔ)引擎的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！