mysql資料庫事務如何運用,mysql資料庫事務怎麼寫

本文目錄一覽：

1、如何在mysql 的存儲過程中使用事務
2、資料庫詳解之事務
3、mysql中事務的主要應用場景是什麼
4、Mysql中的事務是什麼如何使用
5、Mysql資料庫中，事務是指什麼？如何使用該功能？

如何在mysql 的存儲過程中使用事務

6.7 MySQL 事務與鎖定命令

6.7.1 BEGIN/COMMIT/ROLLBACK 句法

預設的，MySQL 運行在 autocommit 模式。這就意味著，當你執行完一個更新時，MySQL 將立刻將更新存儲到磁碟上。

如果你使用事務安全表 (例如 InnoDB、BDB)，通過下面的命令，你可以設置 MySQL 為非 autocommit 模式：

SET AUTOCOMMIT=0

在此之後，你必須使用 COMMIT 來存儲你的更改到磁碟上，或者使用 ROLLBACK ，如果你希望忽略從你的事務開始所做的更改。

如果你希望為一系列語句從 AUTOCOMMIT 模式轉換，你可以使用 START TRANSACTION 或 BEGIN 或 BEGIN WORK 語句：

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summmary=@A WHERE type=1;

COMMIT;

START TRANSACTION 在 MySQL 4.0.11 中被加入；這是被推薦的開始一個特別(ad-hoc)事務的方式，因為這是 ANSI SQL 句法。

注意，如果你使用的是一個非事務安全表，更改會立刻被存儲，不受 autocommit 模式狀態的約束。

當你更新了一個非事務表後，如果你執行一個 ROLLBACK，你將得到一個錯誤 (ER_WARNING_NOT_COMPLETE_ROLLBACK) 作為一個警告。所有事務安全表將被恢復，但是非事務安全表將不會改變。

如果你使用 START TRANSACTION 或 SET AUTOCOMMIT=0，你應該使用 MySQL

二進位日誌做備份以代替老的更新日誌。事務處理被以一個大塊形式存儲在二進位日誌中，在 COMMIT

上面，為了保護回滾的事務，而不是被存儲的。查看章節 4.9.4 二進位日誌。如果您使用起動事務處理或集AUTOCOMMIT=0

，您應該使用MySQL 二進位日誌為備份代替更舊的更新日誌。事務處理存儲在二進位登錄一大塊，做，保證, 滾的事務處理不存儲。參見部分4

。9.4 二進位日誌。

下列命令自動的結束一個事務 (就好像你在執行這個命令之前，做了一個 COMMIT)：

命令命令命令

ALTER TABLE BEGIN CREATE INDEX

DROP DATABASE DROP TABLE RENAME TABLE

TRUNCATE

你可以使用 SET TRANSACTION ISOLATION LEVEL … 改變事務的隔離級。查看章節 6.7.3 SET TRANSACTION 句法。

6.7.2 LOCK TABLES/UNLOCK TABLES 句法

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} …]

…

UNLOCK TABLES

LOCK TABLES 為當前線程鎖定表。UNLOCK TABLES 釋放當前線程擁有的所有鎖定。當線程發出另一個 LOCK TABLES，或當與伺服器的連接被關閉時，被當前線程鎖定的所有表將被自動地解鎖。

為了在 MySQL 4.0.2 使用 LOCK TABLES ，你必須擁有一個全局的 LOCK TABLES 許可權和一個在相關表上的

SELECT 許可權。在 MySQL 3.23 中，你對該表需要有 SELECT、insert、DELETE 和 UPDATE 許可權。

使用 LOCK TABLES 的主要原因是，仿效事務處理或在更新表時得到更快的速度。此後會有更詳細的描述。

如果一個線程在一個表上得到一個 READ 鎖，該線程 (和所有其它線程) 只能從表中讀取。如果一個線程在一個表上得到一個 WRITE 鎖，那麼只有擁有這個鎖的線程可以從表中讀取和寫表。其它的線程被阻塞。

READ LOCAL 和 READ 之間的不同就在於，當鎖被載入時，READ LOCAL 允許非衝突(non-conflicting) INSERT 語句執行。如果當你載入著鎖時從 MySQL 外部操作資料庫文件，這將仍不能被使用。

當你使用 LOCK TABLES 是地，你必須鎖定所有你將使用的表，並且必須使用與你的查詢中將使用的別名相同！如果你在一個查詢中多次使用一個表(用別名)，你必須為每一個別名獲得一個鎖。

WRITE 鎖通過比 READ 鎖有更高的許可權，以確保更新被儘快地處理。這就意味著，如果一個線程獲得一個 READ

鎖，而同時另外一個線程請求一個 WRITE 鎖，並發的 READ 鎖請求將等待直到 WRITE 線程得到了鎖並釋放了它。你可以使用

LOW_PRIORITY WRITE 鎖，當該線程在等待 WRITE 鎖時，它將允許其它的線程獲得 READ 鎖。你應該只使用

LOW_PRIORITY WRITE 鎖，如果你確信這將是最後一次，當沒有線程將擁有 READ 鎖。

LOCK TABLES 工作如下：

以內部定義的次序排序所有被鎖定的表 (從用戶立場說，該次序是不明確的)。

如果一個表被以一個讀鎖和一個寫鎖鎖定，將寫鎖放在讀鎖之前。

一次只鎖定一個表，只到線程得到所有的鎖定。

這個方案是為了確保，表鎖定死鎖釋放。對於這個模式你仍然有些其它事情需要知道：

如果你對一個表使用一個 LOW_PRIORITY WRITE 鎖定，這就意味著，MySQL 將等待這個鎖，直到沒有線程請求一個 READ

鎖。當線程得到了 WRITE 鎖，並等待獲得鎖定表列表中的下一個表的鎖定時，其它所有的線程將等待 WRITE

鎖被釋放。如果這在你的應用程序中會引起一個嚴重的問題，你應該考慮將你的某些錶轉換為事務安全表。

你可以使用 KILL 安全地殺死一個正在表鎖定的線程。查看章節 4.5.5 KILL 句法。

注意，你不應該鎖定你正在對其使用 INSERT DELAYED 的表。這是因為，在這種情況下，INSERT 是通過單獨的線程完成的。

通常，你不需要鎖定任何錶，因為所有單 UPDATE 語句都是原子的；其它的線程無法干擾當前執行的 SQL 語句。當你無論如何希望鎖定表時，這裡有一些情況：

如果你在一束表上運行許多操作，鎖定你將要使用的表，這會更快一些。當然有不利的方面，其它線程將不能更新一個 READ

鎖的表，並且沒有其它線程要以讀取一個 WRITE 鎖的表。在 LOCK TABLES 下，某些事運行得更快一些的原因是，MySQL

將不會轉儲清除被鎖定表鍵高速緩衝，直到 UNLOCK TABLES 被調用 (通常鍵高速緩衝在每個 SQL 語句後都會被轉儲清除)。這將加速在

MyISAM 表上的插入、更新、刪除。

如果你在 MySQL 中正在使用一個不支持事務的存儲引擎，如果你希望能確保沒有其它的線程會出現在一個 SELECT 和一個 UPDATE 之間，你必須使用 LOCK TABLES 。下面的示例顯示為了安全地執行，這裡需要LOCK TABLES ：

mysql LOCK TABLES trans READ, customer WRITE;

mysql SELECT SUM(value) FROM trans WHERE customer_id=some_id;

mysql UPDATE customer SET total_value=sum_from_previous_statement

– WHERE customer_id=some_id;

mysql UNLOCK TABLES;

不使用 LOCK TABLES，將可能發生在 SELECT 和 UPDATE 語句執行期間有另外一個線程可能在 trans 表中插入一行新記錄。

通過使用遞增更新 (UPDATE customer SET value=value+new_value) 或 LAST_INSERT_ID() 函數，你可以在很多情況下避免使用 LOCK TABLES。

你也可以使用用戶級鎖定函數 GET_LOCK() 和 RELEASE_LOCK() 解決一些情況，這些鎖被保存在伺服器上的一個哈希表中，並以

pthread_mutex_lock() 和 pthread_mutex_unlock() 實現以獲得高速度。查看章節 6.3.6.2

輔助功能函數。

查看章節 5.3.1 MySQL 如何鎖定表，以獲取關於鎖定方案的更多信息。

你可以使用 FLUSH TABLES WITH READ LOCK 命令以讀鎖鎖定所有資料庫中的所有表。查看章節 4.5.3 FLUSH 句法。如果你有一個可以及時建立文件快照的文件系統，例如 Veritas，這將是得到備份的非常方便方式。

注意：LOCK TABLES 不是事務安全的，在嘗試鎖定一個表之前，將自動地提交所有的活動事務。

6.7.3 SET TRANSACTION 句法

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

設置全局的、整個會話或下一個事務的事務隔離級。

預設行為是設置下一個(未啟動的)事務的隔離級。如果你使用 GLOBAL

關鍵詞，語句為所有在那個點上建立的新連接設置默認的全局事務隔離級。為了這樣做，你需要有 SUPER 許可權。使用 SESSION

關鍵詞為當前連接所有將來執行的事務設置默認的事務隔離級。

你可以使用 –transaction-isolation=… 為 mysqld 設置默認的全局隔離級。查看章節 4.1.1 mysqld 命令行選項

資料庫詳解之事務

究竟什麼是資料庫的事務，為什麼資料庫需要支持事務，為了實現資料庫事務各種資料庫的是如何設計的。還是只談理解，歡迎大家來討論。

1. 資料庫事務是什麼

事務的定義，已經有太多文章寫過，我就不重複了。我理解的事務就是用來保證數據操作符合業務邏輯要求而實現的一系列功能。換句話說，如果資料庫不支持事務，上面業務系統的程序員就需要自己寫代碼保證相關數據處理邏輯的正確性。而資料庫事務就是把一系列保證資料庫處理邏輯正確性的通用功能在資料庫內實現，並且盡量提高效率。

舉個例子，資料庫最開始普及就是在金融業，銀行的存取款場景就是一個最典型的OLTP資料庫場景，而事務就是設計用來保證類似場景的業務邏輯正確性的。

![事務的四個基本特性]()

**原子性**，如果你要給家人轉賬，必須在你的賬戶里扣掉100塊，在家人賬戶里加上100塊，這兩筆操作需要一起完成，業務邏輯才是正確的。但是程序在做修改的時，肯定會有先後順序，試想一下程序扣了你的錢，這個時候程序崩潰了，家人賬戶的錢沒有加上。那這100塊是不是消失了？你是不是要發瘋？那麼，就把這兩筆操作放進一個事務里，通過原子性保證，這兩筆操作要麼都成功，要麼都失敗。這樣才能保證業務邏輯的正確性。

**一致性**，有很多文章講過一致性，但是很多人會把一致性跟原子性混在一起說。事務的一致性指的是指每一個事務必須保證執行之後所有庫內的規則依舊成立。比如內外鍵，constraint，觸發器等。舉例來說，你在儲蓄卡里有100元，理財賬戶里有100元，基金賬戶有100元，那麼你在資產總和里會看到300元，這個300元必須是其他三個賬戶餘額加在一起得到的。你在給家人轉帳100元是從儲蓄卡里轉出去了100元，那麼在資料庫上可以通過創建觸發器的方式，當儲蓄卡餘額賬戶減100元的同時，把資產總和也同步減去100，不然的話，就會出現邏輯上的錯誤，因為你已經轉走了100塊儲蓄卡餘額，實際資產總和應該是200，如果還是300，資料庫狀態就不一致了。所以實現事務的時候，必須要保證相關聯的觸發器以及其他所有的內部規則都執行成功，事務才能算執行成功。如果在減去資產總時出錯，那麼這筆轉帳交易也不能成功。因為這樣資料庫就會進入不一致的狀態。

那麼這裡跟原子性的區別到底在哪裡呢？原子性是指個多個用戶指令之間必須作為一個整體完成或失敗，而一致性更多是資料庫內的相關數據規則必須同時完成或失敗。

**持久性**，最容易理解的一個，事務只要提交了，那麼對資料庫的修改就會保存下來不會丟了。簡單來說，只要提交了，資料庫就算崩潰了，重啟之後你剛存的100塊依然在你的賬戶里。

**隔離性**，每個事務相對於其他的事務是有一定獨立性的，不能互相影響。因為資料庫需要支持並發的操作來提高效率。在並發操作時，一定要通過操作之間的隔離來保證業務邏輯的正確性。比如，你轉帳100塊給家人，一系列操作的最後一步可能是輸入驗證碼，這個時候轉帳還沒有完成，但是在資料庫里你的賬戶對應的記錄中已經減去100塊，家人賬戶也加了100塊，就等著驗證碼輸入以後，事務提交，完成操作。那麼，這個時候，家人通過手機銀行能夠查到這100塊么？你的答案可能是不能，因為這樣才符合業務邏輯，因為你的轉帳操作還沒有提交，事務還沒有完成。那麼資料庫就應該保證這兩個並發操作之間具有一定的隔離性。

那麼到底應該隔離到什麼程度呢？隔離性又分為4個等級：由低到高依次為Read uncommitted(讀未提交)、Read committed（讀提交）、Repeatable read（可重複讀取）、Serializable（序列化），這四個級別可以逐個解決臟讀、不可重複讀、幻象讀這幾類問題。這些東西是什麼意思？請有興趣的小夥伴自行百度，很多文章都寫的很清楚。

那麼怎麼理解不同的隔離等級呢，首先要理解並發操作，並發操作就是指有不同的用戶同時對一個數據進行讀、寫操作，那麼在這個過程中，每個用戶應該看到什麼數據才能保證業務邏輯的正確性呢？如果是前面存取款的場景，我必須看到的是已經存進來的錢，也就是必須是已經提交的事務。而12306刷火車票呢，你可以看到有10張余票，但是在下單的時候告訴你票賣完了，因為同時有10個用戶把票買掉了，你需要重新刷余票，這個也是可以接受的，也就是說我可以讀到一些虛假的余票，這樣在業務上也沒有什麼問題。那麼在設計這兩個不同系統時，就可以選擇不同的事務隔離級別來實現不同的並發效果。不同的隔離等級就是要在系統的並發性和數據邏輯的嚴謹性之間做出的平衡。

2. 資料庫如何實現事務

資料庫實現事務會有多種不同的方式，但基本的原理類似，比如都需要對事務進行統一的編號處理，都需要記錄事務的狀態（是成功了還是失敗了），都需要在數據存儲的層面對事務進行支持，以明確哪些數據是被哪些事務、插入、修改和刪除的。同時還會記錄事務日誌等，對事務進行系統化的管理以實現數據的原子性，一致性和持久性。

要實現事務的隔離性，最基礎的就是通過加鎖機制把並發操作適當的串列化來保證數據操作的正確邏輯。但是為了要保證系統具有良好的並發性能，必須要在實現事務隔離性時需要找到合理的平衡點。大部分資料庫（包括Oracle，MySQL，Postgres在內）在做並發控制的時候都會採用MVCC（多版本並發控制）的機制來保證系統具有較高的並發性，不同資料庫實現MVCC的具體方案也不盡相同，但其基本原理類似。

3. MVCC實現原理

所謂MVCC，就是資料庫中的同一查詢根據相關事務執行的先後順序以及隔離級別的不同，可能會存在不同版本的結果，通過這樣的手段來保證大部分查詢操作不會被修改操作阻塞並保證數據邏輯的正確性。也就是資料庫通過保存多個版本的數據（歷史數據）來提高系統的並發查詢能力。簡單來說就是用存儲空間來交換並發能力。下面以Postgres為例介紹一下MVCC的一種實現方式幫助大家理解這個重要的資料庫概念。通過下面的圖來解釋Posrgres里最基本的數據可見性是如何實現多版本控制的。

![在這裡插入圖片描述]()

首先，Postgres里的每一個事務都有編號，這裡可以簡單理解為時間順序編號，編號越大的事務發生越晚。然後，資料庫里的每一行記錄都會保存創建這條記錄的事務號（Cre），也會在記錄刪除時保存刪除這條記錄的事務號（Exp），換句話說，只要Exp這裡一列里記錄了事務編號，就說明這條記錄被刪除了。那麼一個事務應該能看見那些記錄呢？Postgres里每一個事務都會保存一個當前系統的事務快照（Snapshot），這個快照里會保存事務創建時當前系統的最高（最晚）事務編號，以及目前還在進行中的事務編號。那麼如上圖所示的一個事務的快照里最高事務編號為100，目前正在進行的事務有25，50和75。那麼對應左邊數據記錄，這6行數據的可見性就如同標註的一般：

第一行，Cre 30，沒有刪除，在100這個時間點，應該能看到。

第二行，Cre 50，沒有刪除，但是50這個事務還沒有提交，正在進行中，所以看不見。

第三行，Cre 110，沒有刪除，但是100這個時間點110事務還沒有發生，所以看不見。

第四行，Cre 30，Exp 80，在80的時候數據被刪掉了，所以看不見。

第五行，Cre 30，Exp 75，在30的時候被創建，75時候被刪掉了，但是75這個事務在100的時候還沒有提交，所以這條記錄在100的時候還沒有刪掉，所以看得見。

第六行，Cre30，Exp 110，在30的時被創建，110時候被刪掉，但是在100時候，110還沒有發生，所以看得見。

綜上，就是這個事務對這六條記錄的可見性，也就是一個數據版本。那麼大家可以看一下如果另一個事務的快照里存的是最高事務編號為110，正在進行的事務為50，那麼它能看到的數據應該是哪幾行呢？同時大家也看到，Postgres里刪除一行數據其實就是在這一行的Exp這個列記錄一個刪除事務的編號，相當於做了一個刪除標記，而數據沒有真正被刪除，因此Postgres資料庫需要定期做數據清理操作（Vacuum）。Pstgres的在現實場景里會比這裡介紹的要複雜，因為我們這裡假定所有的事務最終都是正確提交了，如果存在某些事務沒有提交的情況，那麼可見性就會更加複雜，這裡不再展開了。

資料庫事務是基本的資料庫概念，之前已經有很多很好文章做過介紹，這裡希望能把自己的理解用比較通俗的描述分享給大家，歡迎來討論交流。

mysql中事務的主要應用場景是什麼

事務的使用場景是很廣泛的，舉個簡單的例子

有個買東西的網站，你下單買了某個貨品，那麼資料庫中的訂單表會插入一條你下單的記錄，同時貨物表需要把你所購買貨品的數量進行更新，候新增訂單和更新貨品數量必須是一致的，不應該出現有人下了單但是貨品數量不變，也不應該貨品數量減少了而訂單卻沒有新增這種情況發生

那麼插入訂單信息和更新貨品數量就是一個完整的事務，要麼都執行成功，如果兩個操作中有任意一個操作失敗，則整個流程就應該是失敗的，已經執行的操作就應當回滾

在資料庫中使用事務，可以保證多個資料庫操作的一致性

這就是一個簡單的事務的使用場景

Mysql中的事務是什麼如何使用

事務能保證用戶做的一系列動作，要麼全部成功。如果有一個操作失敗，就回退到修改前。比如你要做下面幾個操作，

1、刪除表A中的某些記錄

2、向B添加一些記錄。

3、修改C表中的一些數據。

使用事務，如果1,2都成功了，3卻失敗了。就會回退到第1步執行前的樣子，ABC表都沒被修改。

Mysql資料庫中，事務是指什麼？如何使用該功能？

什麼是事務？ \x0d\x0a\x0d\x0a事務是邏輯上的一組操作，組成這組操作的各個單元，要不全都成功要不全都失敗，這個特性就是事務 \x0d\x0a\x0d\x0a注意：mysql數據支持事務，但是要求必須是innoDB存儲引擎 \x0d\x0a\x0d\x0a解決這個問題： \x0d\x0a\x0d\x0amysql的事務解決這個問題，因為mysql的事務特性，要求這組操作，要不全都成功，要不全都失敗，這樣就避免了某個操作成功某個操作失敗。利於數據的安全 \x0d\x0a\x0d\x0a如何使用： \x0d\x0a\x0d\x0a（1）在執行sql語句之前，我們要開啟事務 start transaction; \x0d\x0a\x0d\x0a（2）正常執行我們的sql語句 \x0d\x0a\x0d\x0a（3）當sql語句執行完畢，存在兩種情況： \x0d\x0a\x0d\x0a1，全都成功，我們要將sql語句對資料庫造成的影響提交到資料庫中，committ \x0d\x0a\x0d\x0a2，某些sql語句失敗，我們執行rollback（回滾），將對資料庫操作趕緊撤銷 \x0d\x0a\x0d\x0a（注意：mysql數據支持事務，但是要求必須是innoDB存儲引擎） \x0d\x0amysql create table bank(name varchar(20),money decimal(5,1))engine=innodb defau \x0d\x0alt charset=utf8; \x0d\x0a\x0d\x0amysql inset into bank values(‘shaotuo’,1000),(‘laohu’,5000); \x0d\x0a\x0d\x0amysql select*from bank; \x0d\x0a+———+——–+ \x0d\x0a| name | money | \x0d\x0a+———+——–+ \x0d\x0a| shaotuo | 1000.0 | \x0d\x0a| laohu | 5000.0 | \x0d\x0a+———+——–+ \x0d\x0a\x0d\x0a——沒有成功「回滾」執行rollback \x0d\x0amysql start transaction; //開啟事務 \x0d\x0aQuery OK, 0 rows affected (0.00 sec) \x0d\x0a\x0d\x0amysql update bank set money=money+500 where name=’shaotuo’; \x0d\x0aQuery OK, 1 row affected (0.00 sec) \x0d\x0aRows matched: 1 Changed: 1 Warnings: 0 \x0d\x0a\x0d\x0amysql update bank set moey=money-500 where name=’laohu’; \x0d\x0aERROR 1054 (42S22): Unknown column ‘moey’ in ‘field list’ \x0d\x0amysql rollback; //只要有一個不成功，執行rollback操作 \x0d\x0aQuery OK, 0 rows affected (0.01 sec) \x0d\x0a\x0d\x0amysql select*from bank; \x0d\x0a+———+——–+ \x0d\x0a| name | money | \x0d\x0a+———+——–+ \x0d\x0a| shaotuo | 1000.0 | \x0d\x0a| laohu | 5000.0 | \x0d\x0a+———+——–+ \x0d\x0a——成功之後進行commit操作 \x0d\x0amysql start transaction; //開啟事務 \x0d\x0aQuery OK, 0 rows affected (0.00 sec) \x0d\x0a\x0d\x0amysql update bank set money=money+500 where name=’shaotuo’; \x0d\x0aQuery OK, 1 row affected (0.01 sec) \x0d\x0aRows matched: 1 Changed: 1 Warnings: 0 \x0d\x0a\x0d\x0amysql update bank set money=money-500 where name=’laohu’; \x0d\x0aQuery OK, 1 row affected (0.00 sec) \x0d\x0aRows matched: 1 Changed: 1 Warnings: 0 \x0d\x0a\x0d\x0amysql commit; //兩個都成功後執行commit（只要不執行commit，sql語句不會對真實的資料庫造成影響） \x0d\x0aQuery OK, 0 rows affected (0.05 sec) \x0d\x0a\x0d\x0amysql select*from bank; \x0d\x0a+———+——–+ \x0d\x0a| name | money | \x0d\x0a+———+——–+ \x0d\x0a| shaotuo | 1500.0 | \x0d\x0a| laohu | 4500.0 | \x0d\x0a+———+——–+

原創文章，作者：OPGG，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/140599.html