所在位置:首頁 -- 數據庫 -- 正文

mysql dba系統學習(22)數據庫事務詳解


發布時間:2013-11-20  來源:admin

上個星期去面試數據庫管理員的工作,筆試通過之后就是直接的面試,他問了我一個問題,叫我介紹哈數據庫的事務的看法和理解,但是不知所錯的沒有章法的亂答一氣,唉唉,基礎不扎實啊。

下面來好好的學習哈mysqlinnodb引擎的事務相關的知識


 

一,我們為什么需要數據庫事務呢?

這個問題看似很簡單,但是真的能夠說明白還是要看看我們的基本功是不是扎實

例如,用戶A給用戶B通過ATM機轉賬1000元,那么A賬戶上就會少1000元,而B用戶會多1000元

 在這個過程中,兩個環節是關聯的。第一個賬戶劃出款項必須保證正確的存入第二個賬戶,如果第二個環節沒有完成,整個的過程都應該取消,否則就會發生丟失款項的問題。整個交易過程,可以看作是一個事物,成功則全部成功,失敗則需要全部撤消,這樣可以避免當操作的中間環節出現問題時,產生數據不一致的問題。


 

二,數據庫事務的特點

原子性
事務必須是原子工作單元;對于其數據修改,要么全都執行,要么全都不執行。

一致性
事務在完成時,必須使所有的數據都保持一致狀態。在相關數據庫中,所有規則都必須應用于事務的修改,以保持所有數據的完整性。
事務結束時,所有的內部數據結構(如 B 樹索引或雙向鏈表)都必須是正確的。

隔離性
由發并事務所作的修改必須與任何其它并發事務所作的修改隔離。事務查看數據時數據所處的狀態,要么是另一并發事務修改它之前的狀態,
要么是另一事務修改它之后的狀態,事務不會查看中間狀態的數據。這稱為可串行性,因為它能夠重新裝載起始數據,并且重播一系列事務,
以使數據結束時的狀態與原始事務執行的狀態相同。

持久性
事務完成之后,它對于系統的影響是永久性的。該修改即使出現系統故障也將一直保持。


 

三、如何在MYSQL 中使用事務

1、mysql什么引擎可以使用事務

只有InnoDB /BDB 的之類的transaction_safe table 才能支持。

首先需要你的mysql支持innodb引擎,查看你的mysql支持的引擎的語法為:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
mysql> show engines  \G;
*************************** 1. row ***************************
      Engine: ndbcluster
     Support: NO
     Comment: Clustered, fault-tolerant tables
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
8 rows in set (0.00 sec)
ERROR:
No query specified

我們可以看到默認的數據庫引擎是MyISAM不是innodb,那么如何才可以讓其默認的數據庫引擎是innodb呢?

可以在[mysqld] 加入:default_storage_engine=InnoDB;


 

建立InnoDB

 
1
create table ...... engine=innodb;

如果某個表已經創建好了,你可以這樣查看該表使用的數據庫引擎:

 
1
show create table table_name;

如果該表的引擎不是innodb,你可以這樣來修改,將該表使用innodb引擎:

 
1
alter table table_name engine=innodb;
 

2、如何使用事務

 

認為分為兩種:

1、begin rollback,commit .當然有的人用begin /begin work .推薦用START TRANSACTION SQL-99標準啟動一個事務。

 
1
2
3
4
start transaction
update from account
setmoney=money-100 where name='a';
update from account
setmoney=money+100 where name='b';
commit

解釋: 這樣start transaction 手動開啟事務,commit 手動關閉事務。

2、默認的時候autocommit=1 自動提交是開啟的,所以你可以理解為每條語句一輸入到mysqlcommit 了。當你 set autocommit=0 時候,你可以這樣:

 
1
2
3
update from account setmoney=money-100 where name='a';
update from account
setmoney=money+100 where name='b';
commit

// 默認都不提交,只有手動鍵入commit 時候上述都提交


 


 

四,事務舉例

1,創建表student

1
2
mysql> create table student(id int(10),name char(10),msg varchar(50)) engine=innodb ;
Query OK, 0 rows affected (0.05 sec)

2,表student的數據內容

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from student;
+------+----------+------------+
| id   | name     | msg        |
+------+----------+------------+
|    1 | chen     | goof       |
|    2 | zhaoqian | DEDDEFFccc |
|    3 | sunli    | nihaoma    |
|    4 | zhenwang | ngood      |
|    5 | meiguo   | nginx      |
+------+----------+------------+
5 rows in set (0.00 sec)

3,關閉事務自動提交

 

1
2
3
4
5
6
7
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

4,修改數據保存點

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> insert into student values(6,'zhongguo','xiaoping') ;
Query OK, 1 row affected (0.01 sec)
mysql> savepoint s1 ;      //插入數據創建保存點是s1
Query OK, 0 rows affected (0.00 sec)
mysql> update  student set  id=7 where name='meiguo' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> savepoint s2     //修改數據創建保存點s2
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from student  ;
Query OK, 6 rows affected (0.00 sec)
mysql> savepoint s3;    //刪除所有的表內容創建保存點s3
Query OK, 0 rows affected (0.00 sec)

5,當我們覺得我們的操作有誤的話,我們可以rollback事務

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
回滾到s1 就有第六條數據,但是如果上述的創建保存點是在一個session進程的話,一旦rollback到s1那么后面的保存點也不存在了
mysql> rollback to savepoint s1
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+------+----------+------------+
| id   | name     | msg        |
+------+----------+------------+
|    1 | chen     | goof       |
|    2 | zhaoqian | DEDDEFFccc |
|    3 | sunli    | nihaoma    |
|    4 | zhenwang | ngood      |
|    5 | meiguo   | nginx      |
|    6 | zhongguo | xiaoping   |
+------+----------+------------+
6 rows in set (0.00 sec)

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> select * from student;
+------+----------+----------+
| id   | name     | msg      |
+------+----------+----------+
|    6 | zhongguo | xiaoping |
|    1 | chen     | goof     |
|    5 | meiguo   | nginx    |
+------+----------+----------+
3 rows in set (0.00 sec)
mysql> savepoint  good ;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from student;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> rollback to savepoint   good ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+------+----------+----------+
| id   | name     | msg      |
+------+----------+----------+
|    6 | zhongguo | xiaoping |
|    1 | chen     | goof     |
|    5 | meiguo   | nginx    |
+------+----------+----------+
3 rows in set (0.00 sec)


五,事務的ACID(Atomicity \Consistency \Isolation \Durablility)

A: 事務必須是原子(不可分割),要么執行成功進入下一個狀態,要么失敗rollback 到最初狀態。

C在事務開始之前和事務結束以后,數據庫的完整性約束沒有被破壞。 這個一般通過外鍵來約束。

I:一個事務不能知道另外一個事務的執行情況(中間狀態)

D在事務完成以后,該事務所對數據庫所作的更改便持久的保存在數據庫之中,并不會被回滾。

mysql 自己的MyISAM 沒有通過acid 測試,但是InnoDB 可以做到。

在分布式的系統中,通常會有多個線程連接到數據庫中同時對一個表進行操作(這里的同時并不表示同一個時間點,而是同時競爭cpu的資源,至于如何調度,就要看線程和操作系統如何進行調度了),這種情況下如果會話的事物設置不當,就會導致數據混亂,常常會出現以下三種情況(假設現在系統中有兩個會話AB,同時對表student操作)

1,臟讀

session A 操作

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from student;
+------+----------+----------+
| id   | name     | msg      |
+------+----------+----------+
|    6 | zhongguo | xiaoping |
|    1 | chen     | goof     |
|    5 | meiguo   | nginx    |
+------+----------+----------+
3 rows in set (0.00 sec)
mysql> update student set id=2 where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from  student;
+------+----------+----------+
| id   | name     | msg      |
+------+----------+----------+
|    2 | zhongguo | xiaoping |
|    1 | chen     | goof     |
|    5 | meiguo   | nginx    |
+------+----------+----------+
3 rows in set (0.00 sec)

然而在session A 沒有commit 之前session B 查詢 看到的結果卻不是這樣的

1
2
3
4
5
6
7
8
9
10
11
mysql> use test
Database changed
mysql> select * from student;
+------+----------+----------+
| id   | name     | msg      |
+------+----------+----------+
|    6 | zhongguo | xiaoping |
|    1 | chen     | goof     |
|    5 | meiguo   | nginx    |
+------+----------+----------+
3 rows in set (0.00 sec)


2.不可重復讀

1中說明的就是我們不能讀取一個事務的中間狀態。 而重復讀是指我們每次讀取到的結果都要一直。 這個也是mysql 默認的級別。

 

1
2
3
4
5
6
7
mysql> select @@tx_isolation ;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


 

3.虛讀

在一個事務內讀取到了別的事務插入的數據,導致前后讀取不一致。和不可重復讀的區別是:不可重復讀是讀取到了別人對表中的某一條記錄進行了修改,導致前后讀取的數據不一致。 虛讀是前后讀取到表中的記錄總數不一樣,讀取到了其它事務插入的數據。比如現在有 A B 兩個應用程序,他們并發訪問了數據庫中的某一張表,假設表中有 3 條記錄,B 執行查詢操作, 第一次查詢表得到了 3 條記錄。此時 A 對表進行了修改,增加了一條記錄,當 B 再次查詢表的時候,發現多了一條數據。這種情況就造成了 B 的虛讀。但是虛讀是不一定每次都發生的,這種情況是不確定的。為了避免虛讀,我們可以將事物隔離級別設置為 serializable 如果設置成了這種級別,那么數據庫就變成了單線程訪問的數據庫,導致性能降低很多。

一種更易理解的說法是:在一個事務內,多次讀同一個數據。在這個事務還沒有結束時,另一個事務也訪問該同一數據。那么,在第一個事務的兩次讀數據之間。由于第二個事務的修改,那么第一個事務讀到的數據可能不一樣,這樣就發生了在一個事務內兩次讀到的數據是不一樣的,因此稱為不可重復讀,即原始讀取不可重復。

臟讀又稱無效數據的讀出,是指在數據庫訪問中,事務T1將某一值修改,然后事務T2讀取該值,此后T1因為某種原因撤銷對該值的修改,這就導致了T2所讀取到的數據是無效的。臟讀就是指當一個事務正在訪問數據,并且對數據進行了修改,而這種修改還沒有提交到數據庫中,這時,另外一個事務也訪問這個數據,然后使用了這個數據。因為這個數據是還沒有提交的數據,那么另外一個事務讀到的這個數據是臟數據,依據臟數據所做的操作可能是不正確的。


 


 

六,事物的隔離級別

默認的事務隔離級別

1
2
3
4
5
6
7
mysql> show variables like "tx_isolation" ;
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)


1Serializable:可避免臟讀、不可重復讀、虛讀情況的發生。

(2Repeatable read:可避免臟讀、不可重復讀情況的發生。(可重復讀,是 mysql 默認的事務隔離級別)

3Read committed:可避免臟讀情況發生。(讀取已提交的數據)

4Read uncommitted:最低級別,以上情況均無法保證。(讀取到了未提交的數據)


 

當我們將數據庫的隔離級別設置為:Serializable 的時候,雖然可以避免所有并發訪問的問題,但是 Serializable 采用的是單線程來解決并發訪問的問題,也就是說在某一段時間內,只能有一個用戶對數據庫進行操作,導致其它用戶阻塞。導致數據庫的訪問性能很差。

1.讀未提交(Read Uncommitted):這種隔離級別可以讓當前事務讀取到其它事物還沒有提交的數據。這種讀取應該是在回滾段中完成的。通過上面的分析,這種隔離級別是最低的,會導致引發臟讀,不可重復讀,和幻讀。

2.讀已提交(Read Committed):這種隔離級別可以讓當前事務讀取到其它事物已經提交的數據。通過上面的分析,這種隔離級別會導致引發不可重復讀,和幻讀。

3.可重復讀取(Repeatable Read):這種隔離級別可以保證在一個事物中多次讀取特定記錄的時候都是一樣的。通過上面的分析,這種隔離級別會導致引發幻讀。

4.串行(Serializable):這種隔離級別將事物放在一個隊列中,每個事物開始之后,別的事物被掛起。同一個時間點只能有一個事物能操作數據庫對象。這種隔離級別對于數據的完整性是最高的,但是同時大大降低了系統的可并發性。

中国北京单场足球彩票 2019中国女篮决赛时间 老时时彩 福利彩票奖金规则 上证指数现在的点位是多少 重庆时时五星计划 3d稳赚法 云南时时十一选五开奖走势图 双色球走势图表近50期 555彩票 下载 最新助赢彩票计划软件 河北11选5开奖结果查询 七乐彩专家 彩票app开发 天天捕鱼电玩版赢手机 玩彩胆神 重庆时时计划蚂蚁