mysql事務(wù)與存儲引擎實(shí)例分析

這篇文章主要介紹了mysql事務(wù)與存儲引擎實(shí)例分析的相關(guān)知識，內(nèi)容詳細(xì)易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇mysql事務(wù)與存儲引擎實(shí)例分析文章都會有所收獲，下面我們一起來看看吧。

一、MySQL事務(wù)

1、事務(wù)的概念

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

（2）事務(wù)是一個不可分割的工作邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時，事務(wù)是最小的控制單元。

（3）事務(wù)適用于多用戶同時操作的數(shù)據(jù)庫系統(tǒng)的場景，如銀行、保險公司及證券交易系統(tǒng)等等。 （4）事務(wù)通過事務(wù)的整體性以保證數(shù)據(jù)的一致性。

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

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

（1）原子性：指事務(wù)是一個不可再分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生。 a、事務(wù)是一個完整的操作，事務(wù)的各元素是不可分的。

b、事務(wù)中的所有元素必須作為一個整體提交或回滾。

c、如果事務(wù)中的任何元素失敗，則整個事務(wù)將失敗。

（2）一致性：指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。

a、當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài)。

b、在事務(wù)開始前，數(shù)據(jù)庫中存儲的數(shù)據(jù)處于一致狀態(tài)。

c、在正在進(jìn)行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài)。

d、當(dāng)事務(wù)成功完成時，數(shù)據(jù)必須再次回到已知的一致狀態(tài)。

（3）隔離性：指在并發(fā)環(huán)境中，當(dāng)不同的事務(wù)同時操縱相同的數(shù)據(jù)時，每個事務(wù)都有各自的完整數(shù)據(jù)空間。 對數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)。 修改數(shù)據(jù)的事務(wù)可在另一個使用相同數(shù)據(jù)的事務(wù)開始之前訪問這些數(shù)據(jù)，或者在另一-個使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問這些數(shù)據(jù)。

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

a、指不管系統(tǒng)是否發(fā)生故障，事務(wù)處理的結(jié)果都是永久的。

b、一旦事務(wù)被提交，事務(wù)的效果會被永久地保留在數(shù)據(jù)庫中。

總結(jié)：在事務(wù)管理中，原子性是基礎(chǔ)，隔離性是手段，一致性是目的，持久性是結(jié)果。

3、事物之間的互相影響

(1)、臟讀：一個事務(wù)讀取了另一個事務(wù)未提交的數(shù)據(jù)，而這個數(shù)據(jù)是有可能回滾的。

(2)、不可重復(fù)讀：一個事務(wù)內(nèi)兩個相同的查詢卻返回了不同數(shù)據(jù)。這是由于查詢時系統(tǒng)中其他事務(wù)修改的提交而引起的。

(3)、幻讀：一個事務(wù)對一個表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時，另一個事務(wù)也修改這個表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。 那么，操作前一個事務(wù)的用戶會發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。

(4)、丟失更新：兩個事務(wù)同時讀取同一條記錄，A先修改記錄，B也修改記錄 (B不知道A修改過)，B提交數(shù)據(jù)后B的修改結(jié)果覆蓋了A的修改結(jié)果。

二、Mysql及事務(wù)隔離級別

(1) 、read uncommitted：讀取尚未提交的數(shù)據(jù)，不解決臟讀

(2)、 read committed：讀取己經(jīng)提交的數(shù)據(jù)，可以解決臟讀

(3)、 repeatable read：重讀讀取，可以解決臟讀和不可重復(fù)讀-------------mysql默認(rèn)

(4)、 serializable：串行化，可以解決臟讀不可重復(fù)讀和虛讀----------------相當(dāng)于鎖表 注：mysql 默認(rèn)的事務(wù)處理級別是 repeatable read，而 Oracle 和 SQL Server 是 read committed

1、查詢?nèi)质聞?wù)隔離級別

show global variables like '%isolation%';
或
select @@global.tx_isolation;

2、查詢會話事務(wù)隔離級別

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

3、設(shè)置全局事務(wù)隔離級別

set global transaction isolation level read committed;
show global variables like '%isolation%';

4、設(shè)置會話事務(wù)隔離級別

set session transaction isolation level read committed;
show session variables like '%isolation%';

三、事務(wù)控制語句

1、相關(guān)語句

begin;               開啟事務(wù)
commit;              提交事務(wù)，使已對數(shù)據(jù)庫進(jìn)行的所有修改變?yōu)橛谰眯缘?br/>rollback;            回滾事務(wù)，撤銷正在進(jìn)行的所有未提交的修改
savepoint s1;        建立回滾點(diǎn)，s1為回滾點(diǎn)名稱，一個事務(wù)中可以有多個
rollback to s1;      回滾到s1回滾點(diǎn)

2、案例

①、創(chuàng)建表

create database school;
use school;
create table Fmoney(
id int(10) primary key not null,  
name varchar(20),  
money decimal(5,2));

insert into Fmoney values ('1','srs1','100');
insert into Fmoney values ('2','srs2','200');
select * from Fmoney;

②、測試提交事務(wù)

begin;
update Fmoney set money= money - 100 where name='srs2';
commit;
quit

mysql -u root -p
use school;
select * from Fmoney;

③、測試回滾事務(wù)

begin;
update Fmoney set money= money + 100 where name='srs2';
select * from Fmoney;
rollback;

select * from Fmoney;

④、測試多點(diǎn)回滾

begin;
update Fmoney set money= money + 100 where name='srs2';
select * from Fmoney;
savepoint a;
update Fmoney set money= money + 100 where name='srs1';
select * from Fmoney;
savepoint b;
insert into Fmoney values ('3','srs3','300');
select * from Fmoney;

rollback to b;
select * from Fmoney;

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

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

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

四、MySQL 存儲引擎

1、存儲引擎概念介紹

（1）MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件中，每一種技術(shù)都使用不同的存儲機(jī)制、索引技巧、鎖定水平，并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL中稱為存儲引擎。

（2）存儲引擎是MySQL將數(shù)據(jù)存儲在文件系統(tǒng)中的存儲方式或者存儲格式

（3）MySQL 常用的存儲引擎有： a、MylSAM b、InnoDB

（4）MySQL數(shù)據(jù)庫中的組件，負(fù)責(zé)執(zhí)行實(shí)際的數(shù)據(jù)I/O操作

（5）MySQL系統(tǒng)中，存儲引擎處于文件系統(tǒng)之.上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鎯σ?，之后按照各個存儲引擎的存儲格式進(jìn)行存儲。

2、查看系統(tǒng)支持的存儲引擎

show engines;

3、查看表使用的存儲引擎

（1）方法一：直接查看
show table status from 庫名 where name='表名'\G;
例：
show table status from school where name='class'\G;

（2）方法二：進(jìn)入數(shù)據(jù)庫查看
use 庫名;
show create table 表名\G;

例：
use school;
show create table class\G;

4、修改存儲引擎

（1） 方法一：通過 alter table 修改
use 庫名;
alter table 表名 engine=MyISAM;

例：
use school;
alter table class engine=MYISAM;

（2）方法二：通過修改 /etc/my.cnf 配置文件，指定默認(rèn)存儲引擎并重啟服務(wù)
注意：此方法只對修改了配置文件并重啟mysql服務(wù)后新創(chuàng)建的表有效，已經(jīng)存在的表不會有變更。
vim /etc/my.cnf
......
[mysqld]
......
default-storage-engine=INNODB

systemctl restart mysql.service


（3）方法三：通過 create table 創(chuàng)建表時指定存儲引擎
use 庫名;
create table 表名(字段1 數(shù)據(jù)類型,...) engine=MyISAM;

例：
mysql -u root -p
use school;
create table test7(id int(10) not null,name varchar(20) not null) engine=MyISAM;

關(guān)于“mysql事務(wù)與存儲引擎實(shí)例分析”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！相信大家對“mysql事務(wù)與存儲引擎實(shí)例分析”知識都有一定的了解，大家如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道。