一文詳解-MySQL 事務(wù)和鎖

當(dāng)多個用戶訪問同一份數(shù)據(jù)時，一個用戶在更改數(shù)據(jù)的過程中，可能有其他用戶同時發(fā)起更改請求，為保證數(shù)據(jù)庫記錄的更新從一個一致性狀態(tài)變?yōu)榱硗庖粋€一致性狀態(tài)，使用事務(wù)處理是非常必要的，事務(wù)具有以下四個特性：

成都創(chuàng)新互聯(lián)主要從事網(wǎng)站設(shè)計、成都做網(wǎng)站、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)象山,十年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):13518219792

MySQL 提供了多種事務(wù)型存儲引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務(wù)。為了支持事務(wù)，InnoDB 存儲引擎引入了與事務(wù)處理相關(guān)的 REDO 日志和 UNDO 日志，同時事務(wù)依賴于 MySQL 提供的鎖機制

事務(wù)執(zhí)行時需要將執(zhí)行的事務(wù)日志寫入日志文件，對應(yīng)的文件為 REDO 日志。當(dāng)每條 SQL 進行數(shù)據(jù)更新操作時，首先將 REDO 日志寫進日志緩沖區(qū)。當(dāng)客戶端執(zhí)行 COMMIT 命令提交時，日志緩沖區(qū)的內(nèi)容將被刷新到磁盤，日志緩沖區(qū)的刷新方式或者時間間隔可以通過參數(shù) innodb_flush_log_at_trx_commit 控制

REDO 日志對應(yīng)磁盤上的 ib_logifleN 文件，該文件默認為 5MB，建議設(shè)置為 512MB，以便容納較大的事務(wù)。MySQL 崩潰恢復(fù)時會重新執(zhí)行 REDO 日志的記錄，恢復(fù)最新數(shù)據(jù)，保證已提交事務(wù)的持久性

與 REDO 日志相反，UNDO 日志主要用于事務(wù)異常時的數(shù)據(jù)回滾，具體內(nèi)容就是記錄數(shù)據(jù)被修改前的信息到 UNDO 緩沖區(qū)，然后在合適的時間將內(nèi)容刷新到磁盤

假如由于系統(tǒng)錯誤或者 rollback 操作而導(dǎo)致事務(wù)回滾，可以根據(jù) undo 日志回滾到?jīng)]修改前的狀態(tài)，保證未提交事務(wù)的原子性

與 REDO 日志不同的是，磁盤上不存在單獨的 UNDO 日志文件，所有的 UNDO 日志均存在表空間對應(yīng)的 .ibd 數(shù)據(jù)文件中，即使 MySQL 服務(wù)啟動了獨立表空間

在 MySQL 中，可以使用 BEGIN 開始事務(wù)，使用 COMMIT 結(jié)束事務(wù)，中間可以使用 ROLLBACK 回滾事務(wù)。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務(wù)

MySQL 定義了四種隔離級別，指定事務(wù)中哪些數(shù)據(jù)改變其他事務(wù)可見、哪些數(shù)據(jù)該表其他事務(wù)不可見。低級別的隔離級別可以支持更高的并發(fā)處理，同時占用的系統(tǒng)資源更少

InnoDB 系統(tǒng)級事務(wù)隔離級別可以使用以下語句設(shè)置：

查看系統(tǒng)級事務(wù)隔離級別：

InnoDB 會話級事務(wù)隔離級別可以使用以下語句設(shè)置：

查看會話級事務(wù)隔離級別：

在該隔離級別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。讀取未提交的數(shù)據(jù)稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個事務(wù)，在 B 事務(wù)更新但未提交之前，A 事務(wù)讀取到了更新后的數(shù)據(jù)，但由于 B 事務(wù)回滾，導(dǎo)致 A 事務(wù)出現(xiàn)了臟讀現(xiàn)象

所有事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變，此級別可以解決臟讀，但也會導(dǎo)致不可重復(fù)讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務(wù)，A事務(wù)讀取了 B 事務(wù)的數(shù)據(jù)，在 B 事務(wù)更新并提交后，A 事務(wù)又讀取到了更新后的數(shù)據(jù)，此時就出現(xiàn)了同一 A 事務(wù)中的查詢出現(xiàn)了不同的查詢結(jié)果

MySQL 默認的事務(wù)隔離級別，能確保同一事務(wù)的多個實例在并發(fā)讀取數(shù)據(jù)時看到同樣的數(shù)據(jù)行，理論上會導(dǎo)致一個問題，幻讀（Phontom Read）。例如，第一個事務(wù)對一個表中的數(shù)據(jù)做了修改，這種修改會涉及表中的全部數(shù)據(jù)行，同時第二個事務(wù)也修改這個表中的數(shù)據(jù)，這次的修改是向表中插入一行新數(shù)據(jù)，此時就會發(fā)生操作第一個事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行

InnoDB 通過多版本并發(fā)控制機制（MVCC）解決了該問題：InnoDB 通過為每個數(shù)據(jù)行增加兩個隱含值的方式來實現(xiàn)，這兩個隱含值記錄了行的創(chuàng)建時間、過期時間以及每一行存儲時間發(fā)生時的系統(tǒng)版本號，每個查詢根據(jù)事務(wù)的版本號來查詢結(jié)果

通過強制事務(wù)排序，使其不可能相互沖突，從而解決幻讀問題。簡而言之，就是在每個讀的數(shù)據(jù)行上加上共享鎖實現(xiàn)，這個級別會導(dǎo)致大量的超時現(xiàn)象和鎖競爭，一般不推薦使用

為了解決數(shù)據(jù)庫并發(fā)控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對并發(fā)操作進行控制，因此產(chǎn)生了鎖

共享鎖的粒度是行或者元組（多個行），一個事務(wù)獲取了共享鎖以后，可以對鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行讀操作

排他鎖的粒度與共享鎖相同，一個事務(wù)獲取排他鎖以后，可以對鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行寫操作

有兩個事務(wù) A 和 B，如果事務(wù) A 獲取了一個元組的共享鎖，事務(wù) B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務(wù) A 釋放共享鎖之后。如果事務(wù) A 獲取了一個元組的排他鎖，事務(wù) B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之后

意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務(wù)有意對數(shù)據(jù)上共享鎖或者排他鎖。有意表示事務(wù)想執(zhí)行操作但還沒真正執(zhí)行

鎖的粒度主要分為表鎖和行鎖

表鎖的開銷最小，同時允許的并發(fā)量也是最小。MyISAM 存儲引擎使用該鎖機制。當(dāng)要寫入數(shù)據(jù)時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執(zhí)行時使用的也是表鎖

行鎖可以支持最大的并發(fā)，InnoDB 存儲引擎使用該鎖機制。如果要支持并發(fā)讀/寫，建議采用 InnoDB 存儲引擎

Mysql中的事務(wù)是什么如何使用

（1）在執(zhí)行sql語句之前，我們要開啟事務(wù) start transaction;

（2）正常執(zhí)行我們的sql語句

（3）當(dāng)sql語句執(zhí)行完畢，存在兩種情況：

1，全都成功，我們要將sql語句對數(shù)據(jù)庫造成的影響提交到數(shù)據(jù)庫中，committ

2，某些sql語句失敗，我們執(zhí)行rollback（回滾），將對數(shù)據(jù)庫操作趕緊撤銷

（注意：mysql數(shù)據(jù)支持事務(wù)，但是要求必須是innoDB存儲引擎）

mysql create table bank(name varchar(20),money decimal(5,1))engine=innodb defau

lt charset=utf8;

mysql inset into bank values('shaotuo',1000),('laohu',5000);

mysql select*from bank;

+---------+--------+

| name | money |

+---------+--------+

| shaotuo | 1000.0 |

| laohu | 5000.0 |

+---------+--------+

------沒有成功“回滾”執(zhí)行rollback

mysql start transaction; //開啟事務(wù)

Query OK, 0 rows affected (0.00 sec)

mysql update bank set money=money+500 where name='shaotuo';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql update bank set moey=money-500 where name='laohu';

ERROR 1054 (42S22): Unknown column 'moey' in 'field list'

mysql rollback; //只要有一個不成功，執(zhí)行rollback操作

Query OK, 0 rows affected (0.01 sec)

Mysql數(shù)據(jù)庫中，事務(wù)是指什么？如何使用該功能？

MySQL 事務(wù)

什么是事務(wù)？

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中，你刪除一個人員，你既需要刪除人員的基本資料，也要刪除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫操作語句就構(gòu)成一個事務(wù)！

在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務(wù)。

事務(wù)處理可以用來維護數(shù)據(jù)庫的完整性，保證成批的 SQL 語句要么全部執(zhí)行，要么全部不執(zhí)行。

事務(wù)用來管理 insert,update,delete 語句

一般來說，事務(wù)是必須滿足4個條件（ACID）：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

原子性：一個事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結(jié)束在中間某個環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯誤，會被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個事務(wù)從來沒有執(zhí)行過一樣。

一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。

隔離性：數(shù)據(jù)庫允許多個并發(fā)事務(wù)同時對其數(shù)據(jù)進行讀寫和修改的能力，隔離性可以防止多個事務(wù)并發(fā)執(zhí)行時由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。

持久性：事務(wù)處理結(jié)束后，對數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會丟失。

在 MySQL 命令行的默認設(shè)置下，事務(wù)都是自動提交的，即執(zhí)行 SQL 語句后就會馬上執(zhí)行 COMMIT 操作。因此要顯式地開啟一個事務(wù)務(wù)須使用命令 BEGIN 或 START TRANSACTION，或者執(zhí)行命令 SET AUTOCOMMIT=0，用來禁止使用當(dāng)前會話的自動提交。

from 樹懶學(xué)堂 - 一站式數(shù)據(jù)知識平臺

mysql 查當(dāng)前有哪些分布式事務(wù)

查看 MySQL 客戶端的事務(wù)提交方式命令：select @@autocommit;

修改 MySQL 客戶端的事務(wù)提交方式為手動提交命令：set @@autocommit = 0;

（注：0 表示手動提交，即使用 MySQL 客戶端執(zhí)行 SQL 命令后必須使用commit命令執(zhí)行事務(wù)，否則所執(zhí)行的 SQL 命令無效，如果想撤銷事務(wù)則使用 rollback 命令。1 表示自動提交，即在 MySQL 客戶端不在需要手動執(zhí)行 commit 命令。）

MySQL 在自動提交模式下，每個 SQL 語句都是一個獨立的事務(wù)。

注意：

1、手動設(shè)置set @@autocommit = 0，即設(shè)定為非自動提交模式，只對當(dāng)前的mysql命令行窗口有效，打開一個新的窗口后，默認還是自動提交；

2、對于非自動提交模式，比如在命令行中添加一條記錄，退出命令行后在重新打開命令行，之前插入的記錄是不在的。（用select * from + 表名 驗證一下就可以了）

MySQL中的大表和大事務(wù)

千萬行數(shù)據(jù)，表數(shù)據(jù)巨大。

? ? ? ? ? ? 1、慢查詢的產(chǎn)生：很難在一定時間內(nèi)過濾出所需要的數(shù)據(jù)。

? ? ? ? ? ? 2、建立索引需要更長的時間。

Mysql版本5.5 建立索引會鎖表

Mysql版本=5.5 誰讓不會鎖表，但會引起主從延遲。

? ? ? ? ? ? 3、修改表結(jié)構(gòu)需要長時間鎖定表

? ? ? ? ? ? 4、會造成長時間的主從延遲。

? ? ? ? ? ? 5、影響正常的數(shù)據(jù)操作。

1、分庫分表把一張大表分為多個小表

難點：

分表主鍵的選擇

分表后跨分區(qū)數(shù)據(jù) 的查詢和統(tǒng)計

2、大表的歷史數(shù)據(jù)歸檔

? ? ? ? ? ? ? ?好處：

減少對前后端業(yè)務(wù)的影響。

? ? ? ? ? ? ? ?難點：

歸檔時間點的選擇

如何進行歸檔操作。

運行時間比較長，操作的數(shù)據(jù)比較多的事務(wù)。

鎖定太多的數(shù)據(jù)，造成大量的阻塞和鎖超時。

? ? ? ? 回滾時間比較長

執(zhí)行時間比較長，容易造成主從延遲。

避免一次處理太多的數(shù)據(jù)。

移除不必要在事務(wù)中操作的select操作

分享文章：mysql大事務(wù)怎么看 mysql長事務(wù)怎么處理
本文路徑：http://jinyejixie.com/article32/hpcosc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、、網(wǎng)站改版、網(wǎng)站排名、網(wǎng)站內(nèi)鏈、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)