MySQL 8.0數(shù)據(jù)庫管理與應用 課件第7-9章 事務與鎖定、備份與恢復、用戶與角色

第7章事務與鎖定7.1.1事務概述事務是指作為單個邏輯工作單元執(zhí)行的一個或多個SQL語句。如果某一事務成功，則在該事務中進行的所有數(shù)據(jù)更改都會提交并成為數(shù)據(jù)庫中的永久組成部分；如果事務遇到錯誤而且撤銷或回滾，則所有數(shù)據(jù)更改都會被清除。一個邏輯工作單元必須有4個屬性，稱為原子性、一致性、隔離性和持久性（ACID）屬性，只有這樣才能成為一個事務。1.原子性（A）事務必須是原子工作單元，整個事務中的所有操作，要么全部執(zhí)行，要么全部都不執(zhí)行，不可能停滯在某個中間環(huán)節(jié)。如果事務在執(zhí)行過程中發(fā)生錯誤，會被返回到事務開始前的狀態(tài)，就像這個事務從來沒有執(zhí)行過一樣。2.一致性（C）當事務完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。3.隔離性（I）由并發(fā)事務所做的修改必須與任何其他并發(fā)事務所做的修改隔離。4.持久性（D）完成完全持久的事務之后，它的影響將永久存在于系統(tǒng)中。7.1事務7.1.2設置自動提交模式默認情況下，MySQL是在啟用自動提交模式的情況下運行的。使用SETautocommit語句來禁用或啟用當前會話的默認自動提交模式：SETautocommit={0|1}如果將系統(tǒng)變量autocommit設置為1，則對表的所有更改都會立即生效。如果將該變量設置為0，則禁用自動提交模式。變量autocommit是一個會話變量，必須對每個會話進行設置。默認情況下，客戶端連接將以autocommit設置為1開始。如果要對每個新連接禁用自動提交模式，則需要對全局自動提交值進行設置。使用--autocommit=0選項啟動MySQL服務器：mysqld--autocommit=0使用MySQL配置文件設置全局變量：[mysqld]autocommit=07.1事務7.1.3開始事務使用STARTTRANSACTION語句將使自動提交模式保持禁用狀態(tài)，一直到使用COMMIT或ROLLBACK語句結(jié)束事務，自動提交模式恢復為先前的狀態(tài)，語法格式如下。STARTTRANSACTION[事務特征[,事務特征]...]事務特征：{WITHCONSISTENTSNAPSHOT|READWRITE|READONLY}以BEGIN和BEGINWORK作為STARTTRANSACTION的別名來啟動事務：BEGIN[WORK]7.1事務7.1.4提交事務使用COMMIT語句可以提交當前事務，使其更改成為永久更改，語法格式如下。COMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE]COMMIT語句支持可選的WORK關鍵字，也支持CHAIN和RELEASE子句。CHAIN和RELEASE子句可以用于對事務完成的附加控制。ANDCHAIN子句導致新事務在當前事務結(jié)束后立即開始，并且新事務與剛剛終止的事務具有相同的隔離級別。新事務還使用與剛剛終止的事務相同的訪問模式（READWRITE或READONLY）。RELEASE子句使服務器在終止當前事務后斷開當前客戶端會話。使用NO關鍵字可以禁止CHAIN或RELEASE完成。如果系統(tǒng)變量completion_type默認設置為CHAIN或RELEASE完成，則NO選項是很有用的。7.1事務7.1.5回滾事務使用ROLLBACK語句可以回滾當前事務，撤消其更改，語法格式如下。ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE]ROLLBACK語句支持可選的WORK關鍵字，也支持CHAIN和RELEASE子句。CHAIN和RELEASE子句可以用于對事務完成的附加控制，請參閱COMMIT語句中的說明。某些語句是無法回滾的。這些語句包括數(shù)據(jù)定義語言（DDL）語句，例如創(chuàng)建或刪除數(shù)據(jù)庫的語句，創(chuàng)建、刪除或更改表或存儲例程的語句。應該將事務設計為不包括這一類語句。如果在無法回滾的事務的早期發(fā)出語句，而以后出現(xiàn)另一個語句失敗，則在這種情況下無法通過發(fā)出ROLLBACK語句來回滾事務的完整效果。回滾的事務不會被記錄。對非事務性表的修改是無法回滾的。如果回滾的事務包括對非事務性表的修改，則在末尾使用ROLLBACK語句記錄整個事務，以確保復制對非事務性表的修改。7.1事務7.1.6事務保存點1.設置事務保存點SAVEPOINT保存點名稱如果當前事務具有相同名稱的保存點，則刪除舊保存點并設置新保存點。2.回滾到事務保存點ROLLBACK[WORK]TO[SAVEPOINT]保存點名稱設置保存點后當前事務對行進行的修改在回滾中撤消。如果不存在具有指定名稱的保存點，則ROLLBACKTOSAVEPOINT語句返回錯誤。3.刪除事務保存點RELEASESAVEPOINT保存點名稱刪除指定的保存點不會發(fā)生提交或回滾事務。如果保存點不存在則會出錯。7.1事務7.1.7設置事務特征使用SETTRANSACTION語句可以設置事務的隔離級別和訪問模式，語法格式如下。SET[GLOBAL|SESSION]TRANSACTION事務特征[,事務特征]...事務特征{ISOLATIONLEVEL隔離級別|READWRITE|READONLY}隔離級別：{REPEATABLEREAD|READCOMMITTED|READUNCOMMITTED|SERIALIZABLE}7.1事務7.2.1鎖定級別表級鎖定

這是一種特殊的鎖定，整個表都被用戶鎖定，其他用戶不能向表中插入行，甚至從表中獲取數(shù)據(jù)也會受到限制。MyISAM表僅支持表級鎖定，當涉及大量讀操作而不是寫操作時，表級鎖定提供了優(yōu)于頁行鎖定和行級鎖定的性能。頁級鎖定：

MySQL鎖定表中的某些行（稱為頁）。被鎖定的行只對鎖定最初的線程是可行的，如果另外一個線程要向這些行中寫數(shù)據(jù)，它就必須等到鎖被釋放。不過，其他頁中的行仍然是可以使用的。行級鎖定：

與表級鎖定或頁級鎖定相比，行級鎖定提供了更精細的控制。在這種情況下，只有線程使用的行是被鎖定的，表中的其他行對于其他線程都是可用的。在多用戶環(huán)境中，行級鎖定降低了線程之間的沖突，可以使多個用戶同時從同一個表中讀取數(shù)據(jù)甚至寫數(shù)據(jù)。InnoDB表類型在事務中自動執(zhí)行行級鎖定。死鎖

當多個用戶同時訪問一個數(shù)據(jù)庫時，死鎖是一種經(jīng)常遇到的現(xiàn)象。如果兩個用戶都在等待對方的數(shù)據(jù)，就會產(chǎn)生一個死鎖。假如用戶user1在R1行上定義了一個鎖，并且希望在R2行上也放置一個鎖，與此同時用戶user2是R2行上的一個鎖的擁有者，并且希望在R1行上也放置一個鎖，則這兩個用戶相互等待，從而產(chǎn)生死鎖。在數(shù)據(jù)庫應用開發(fā)中，要盡量降低發(fā)生死鎖的概率。7.2鎖定7.2.2獲取表級鎖定使用LOCKTABLES語句顯式獲取當前客戶端會話的表級鎖定：LOCKTABLES表名[[AS]別名]鎖定類型[,表名[[AS]別名]鎖定類型]...鎖定類型：{READ[LOCAL]|[LOW_PRIORITY]WRITE}使用LOCKTABLES語句可以為基表或視圖獲取表級鎖定。執(zhí)行該語句時必須擁有對每個要鎖定對象的LOCKTABLES權限和SELECT權限。對于視圖鎖而言，LOCKTABLES語句將視圖中使用的所有基表添加到要鎖定的表集當中并自動鎖定它們。如果使用LOCKTABLES顯式鎖定表，則觸發(fā)器中使用的任何表也將被隱式鎖定。7.2鎖定7.2.3釋放表級鎖定使用UNLOCKTABLES語句顯式釋放當前會話持有的任何表級鎖定:UNLOCKTABLESUNLOCKTABLES語句還可以用于釋放使用FLUSHTABLESWITHREADLOCK語句獲取的全局讀取鎖定，這種形式的FLUSHTABLES語句可以鎖定所有數(shù)據(jù)庫中的所有表。當釋放會話持有的表級鎖定時，它們將會同時釋放。會話可以使用UNLOCKTABLES語句顯式釋放其鎖定，也可以在某些條件下隱式釋放鎖定。如果在一個會話中發(fā)出LOCKTABLES語句，以便在已經(jīng)持有鎖定的情況下獲取鎖定，則在授予新的鎖定之前將會隱式釋放其現(xiàn)有鎖定。如果在會話中開始事務（例如使用STARTTRANSACTION語句），則會隱式執(zhí)行UNLOCKTABLES語句，這會導致釋放現(xiàn)有鎖定。7.2鎖定7.2.4鎖定與事務的交互LOCKTABLES和UNLOCKTABLES與事務的相互作用表現(xiàn)在以下幾個方面。（1）LOCKTABLES不是事務安全的，并且在嘗試鎖定表之前隱式提交任何活動事務。（2）UNLOCKTABLES隱式提交任何活動事務，但僅當LOCKTABLES用于獲取表級鎖定時。例如，在以下語句序列中，UNLOCKTABLES釋放全局讀鎖但不會提交事務，因為沒有表級鎖定是有效的：（3）當開始事務（例如使用STARTTRANSACTION語句）時，將隱式提交任何當前事務并釋放現(xiàn)有表級鎖定。（4）具有READLOCK的FLUSHTABLES獲取全局讀取鎖定而不是表級鎖定，因此在表級鎖定和隱式提交方面，它不受與LOCKTABLES和UNLOCKTABLES相同的行為約束。例如，STARTTRANSACTION不會釋放全局讀鎖定。（5）隱式導致提交事務的其他語句不會釋放現(xiàn)有的表級鎖定。（6）對事務表（如InnoDB表）使用LOCKTABLES和UNLOCKTABLES的正確方法是使用SETautocommit=0禁用自動提交模式，而不是使用STARTTRANSACTION開始一個事務，然后使用LOCKTABLES，并且在明確提交事務之前不要調(diào)用UNLOCKTABLES。（7）執(zhí)行ROLLBACK語句不會釋放表級鎖定。7.2鎖定7.2.5表級鎖定與觸發(fā)器如果使用LOCKTABLES語句顯式鎖定表，則觸發(fā)器中使用的任何表也會隱式鎖定。（1）鎖定與使用LOCKTABLES語句顯式獲取的鎖定時間相同。（2）觸發(fā)器中使用的表上的鎖定取決于該表是否僅用于讀取。如果是這樣，則讀取鎖定就足夠了，否則使用寫入鎖定。（3）如果為了讀取數(shù)據(jù)而使用LOCKTABLES語句顯式鎖定表，又要為寫入數(shù)據(jù)需要而鎖定表（因為它可能在觸發(fā)器中被修改），則將執(zhí)行寫入鎖定而不是讀取鎖定。換言之，由于表在觸發(fā)器中的出現(xiàn)而需要隱式寫入鎖定，這將導致表的顯式讀取鎖定請求轉(zhuǎn)換為寫入鎖定請求。7.2鎖定第8章備份與恢復8.1.1SELECT...INTOOUTFILESELECT...INTOOUTFILE語句可以將通過查詢選定的行寫入文件中，并通過指定列和行的結(jié)束符來指定輸出格式，語法格式如下。SELECT[列名列表]FROM表名[WHERE條件]INTOOUTFILE'文件名'導出選項

|DUMPFILE'文件名'導出選項：

[FIELDSTERMINATEDBY'字符串'[ENCLOSEDBY'字符'][OPTIONALLYENCLOSEDBY'字符'][ESCAPEDBY'字符'][LINESSTARTINGBY'字符串'[TERMINATEDBY'字符串']]8.1使用SQL語句8.1.2LOADDATAINFILE使用LOADDATAINFILE語句將文件中的數(shù)據(jù)讀回表中：LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'文件名'[REPLACE|IGNORE]INTOTABLE表名[CHARACTERSET字符集名][{FIELDS|COLUMNS}[TERMINATEDBY'字符串'][[OPTIONALLY]ENCLOSEDBY'字符'][ESCAPEDBY'轉(zhuǎn)義字符']][LINES[STARTINGBY'字符串'][TERMINATEDBY'字符串']][IGNORE行數(shù){LINES|ROWS}][(列名或用戶變量[,...])][SET列名={表達式|DEFAULT}[,...]}]]8.1使用SQL語句8.2.1mysqldumpmysqldump客戶端實用程序用于執(zhí)行數(shù)據(jù)庫的邏輯備份，它可以生成一組SQL語句，即根據(jù)要備份的表結(jié)構生成相應的CREATE語句，并將表中的所有記錄轉(zhuǎn)換成相應的INSERT語句，表的結(jié)構和數(shù)據(jù)以SQL語句的形式存儲在腳本文件中，通過執(zhí)行該腳本文件可以重現(xiàn)原始數(shù)據(jù)庫對象定義和表數(shù)據(jù)。1.mysqldump的調(diào)用方式備份單個數(shù)據(jù)庫及其表結(jié)構和數(shù)據(jù)：

mysqldump[選項]數(shù)據(jù)庫名[表名...]備份多個數(shù)據(jù)庫：

mysqldump[選項]--databases數(shù)據(jù)庫名...備份MySQL服務器上的所有數(shù)據(jù)庫：

mysqldump[選項]--all-databases2.mysqldump命令選項mysqldump命令行工具有很多選項，這些選項用于控制數(shù)據(jù)庫轉(zhuǎn)儲過程的各個方面，包括服務器連接、配置文件、數(shù)據(jù)定義、調(diào)試、幫助信息、國際化、復制、輸出格式、過濾、性能以及事務等等。8.2使用客戶端工具8.2.2mysqlimportmysqlimport客戶端程序提供了LOADDATAINFILEQL語句的一個命令行接口，它的大多數(shù)選項直接對應LOADDATAINFILE子句。調(diào)用mysqlimport的命令格式如下。mysqlimport[選項]數(shù)據(jù)庫名文本文件1[文本文件2...]對于在命令行中所指定的每個文本文件，mysqlimport將去掉文件名的擴展名并使用結(jié)果來確定將導入文件內(nèi)容的表名。例如，文件patient.txt、patient.text或patient均導入表patient。8.2使用客戶端工具第9章用戶與角色9.1.1創(chuàng)建用戶CREATEUSER語句來創(chuàng)建一個或多個用戶賬戶：CREATEUSER[IFNOTEXISTS]用戶[身份驗證選項][,用戶[身份驗證選項]]...DEFAULTROLE角色[,角色]...[REQUIRE{NONE|SSL/TLS選項[[AND]SSL/TLS選項]...}][WITH資源選項[資源選項]...][密碼選項|鎖定選項]...用戶：'用戶名'@'主機名'9.1用戶管理9.1.1創(chuàng)建用戶身份驗證選項：{IDENTIFIEDBY'身份驗證字符串'|IDENTIFIEDWITH身份驗證插件|IDENTIFIEDWITH身份驗證插件BY'身份驗證字符串'|IDENTIFIEDWITH身份驗證插件AS'哈希字符串'}SSL/TLS選項：{SSL|X509|CIPHER'密碼'|ISSUER'頒發(fā)者'|SUBJECT'主題'}資源選項：{MAX_QUERIES_PER_HOURcount|MAX_UPDATES_PER_HOURcount|MAX_CONNECTIONS_PER_HOURcount|MAX_USER_CONNECTIONScount}9.1用戶管理9.1.1創(chuàng)建用戶密碼選項：{PASSWORDEXPIRE[DEFAULT|NEVER|INTERVALNDAY]|PASSWORDHISTORY{DEFAULT|N}|PASSWORDREUSEINTERVAL{DEFAULT|NDAY}|PASSWORDREQUIRECURRENT[DEFAULT|OPTIONAL]}鎖定選項：{ACCOUNTLOCK|ACCOUNTUNLOCK}9.1用戶管理9.1.2修改用戶使用ALTERUSER語句來修改現(xiàn)有MySQL賬戶的身份驗證、角色、SSL/TLS、資源限制以及密碼管理屬性，并啟用賬戶鎖定和解鎖：ALTERUSER[IFEXISTS]用戶[身份驗證選項][,用戶[身份驗證選項]]...[REQUIRE{NONE|SSL/TLS選項[[AND]SSL/TLS選項]...}][WITH資源選項[資源選項]...][密碼選項|鎖定選項]...用戶：'用戶名'@'主機名'身份驗證選項：{IDENTIFIEDBY'身份驗證字符串'[REPLACE'當前身份驗證字符串']|IDENTIFIEDWITH身份驗證插件|IDENTIFIEDWITH身份驗證插件BY'身份驗證字符串'[REPLACE'當前身份驗證字符串']|IDENTIFIEDWITH身份驗證插件AS'哈希字符串’}9.1用戶管理9.1.2修改用戶SSL/TLS選項：{SSL|X509|CIPHER'密碼'|ISSUER'頒布者'|SUBJECT'主題'}資源選項：{MAX_QUERIES_PER_HOURcount|MAX_UPDATES_PER_HOURcount|MAX_CONNECTIONS_PER_HOURcount|MAX_USER_CONNECTIONScount}密碼選項：{PASSWORDEXPIRE[DEFAULT|NEVER|INTERVALNDAY]|PASSWORDHISTORY{DEFAULT|N}|PASSWORDREUSEINTERVAL{DEFAULT|NDAY}|PASSWORDREQUIRECURRENT[DEFAULT|OPTIONAL]}鎖定選項：{ACCOUNTLOCK|ACCOUNTUNLOCK}9.1用戶管理9.1.3重命名用戶使用RENAMEUSER語句對現(xiàn)有MySQL用戶進行重命名：RENAMEUSER舊用戶TO新用戶[,舊用戶TO新用戶]...其中每個賬戶名使用'用戶名'@'主機名'格式指定。如果省略賬戶名的主機名部分，則默認為'％'。如果不存在的舊賬戶或者已經(jīng)存在新賬戶，則會出錯。要使用RENAMEUSER語句，必須具有全局CREATEUSER權限或mysql系統(tǒng)數(shù)據(jù)庫的UPDATE權限。啟用read_only系統(tǒng)變量時，RENAMEUSER還需要CONNECTION_ADMIN或SUPER權限。RENAMEUSER使舊用戶擁有的權限成為新用戶持有的權限。但是，RENAMEUSER不會自動刪除舊用戶創(chuàng)建的數(shù)據(jù)庫或?qū)ο蟆＿@包括DEFINER屬性為舊用戶命名的存儲程序或視圖。如果在定義的安全性上下文中執(zhí)行，則嘗試訪問此類對象可能會產(chǎn)生錯誤。9.1用戶管理9.1.4修改密碼使用SETPASSWORD語句可以為MySQL用戶賬戶分配密碼，語法格式如下。SETPASSWORD[FOR用戶]='身份驗證字符串'[REPLACE'當前身份驗證字符串']設置指定賬戶的密碼（帶有FOR子句）需要mysql系統(tǒng)數(shù)據(jù)庫的UPDATE權限。為自己設置密碼（對于沒有FOR子句的非用戶賬戶）不需要特殊權限。啟用read_only系統(tǒng)變量時，除了任何其他所需權限外，SETPASSWORD還需要CONNECTION_ADMIN或SUPER權限。如果給定REPLACE子句，則必須指定要替換的當前賬戶密碼。如果需要對賬戶密碼進行修改，則必須提供當前密碼。如果給出了該子句但與當前密碼不匹配，則語句失敗。只有在更改當前用戶的賬戶密碼時才能指定REPLACE子句。在SETPASSWORD語句中，F(xiàn)OR子句是可選的。如果使用FOR子句，則可以設置指定賬戶的密碼。例如：SETPASSWORDFOR'jeffrey'@'localhost'='auth_string';如果沒有使用FOR子句，則設置當前用戶的密碼。例如：SETPASSWORD='auth_string';使用非賬戶賬戶連接到服務器的任何客戶端都可以更改該賬戶的密碼。9.1用戶管理9.1.5刪除用戶在MySQL中，可以使用DROPUSER語句刪除一個或多個MySQL用戶及其權限，從所有授權表中刪除用戶的權限行，語法格式如下。DROPUSER[IFEXISTS]用戶[,用戶]...要使用DROPUSER語句，必須具有全局CREATEUSER權限或mysql系統(tǒng)數(shù)據(jù)庫的DELETE權限。啟用read_only系統(tǒng)變量時，DROPUSER還需要CONNECTION_ADMIN或SUPER權限。DROPUSER對所有命名用戶都成功或回滾，如果發(fā)生任何錯誤則無效。默認情況下，如果嘗試刪除不存在的用戶，則會發(fā)生錯誤。如果給出了IFEXISTS子句，則該語句會為每個不存在的命名用戶生成警告而不是錯誤。如果語句執(zhí)行成功，則將語句寫入二進制日志，如果失敗則不寫入。在這種情況下，發(fā)生回滾并且不進行任何更改。每個用戶賬戶使用'用戶名'@'主機名'格式。如果省略主機名部分，則默認為'%'。DROPUSER語句不會自動關閉任何打開的用戶會話。相反，如果刪除了具有打開會話的用戶，則該語句在該用戶的會話關閉之前不會生效。當會話關閉后，用戶將被刪除，該用戶下次嘗試登錄將失敗。DROPUSER不會自動刪除舊用戶創(chuàng)建的數(shù)據(jù)庫或?qū)ο?。這包括存儲程序或視圖，DEFINER屬性為已刪除的命名用戶。如果在定義的安全性上下文中執(zhí)行，則嘗試訪問此類對象可能會產(chǎn)生錯誤。9.1用戶管理9.2.1創(chuàng)建角色在MySQL中，可以使用CREATEROLE語句創(chuàng)建一個或多個角色，語法格式如下。CREATEROLE[IFNOTEXISTS]角色[,角色]...要使用CREATEROLE語句，必須具有全局CREATEROLE或CREATEUSER權限。啟用read_only系統(tǒng)變量時，CREATEROLE還需要CONNECTION_ADMIN或SUPER權限。創(chuàng)建時的角色已被鎖定，沒有密碼，并被分配了默認的身份驗證插件。CREATEROLE對所有命名角色成功或回滾，如果發(fā)生任何錯誤則無效。默認情況下，如果嘗試創(chuàng)建已存在的角色，則會發(fā)生錯誤。如果給出了IFNOTEXISTS子句，則該語句會為已存在的每個命名角色生成警告而不是錯誤。如果成功，則將語句寫入二進制日志，但如果失敗則不寫入。在這種情況下，發(fā)生回滾并且不進行任何更改。寫入二進制日志的語句包括所有命名角色。如果給出IFNOTEXISTS子句，則包括已存在且未創(chuàng)建的角色。9.2角色管理9.2.2授予權限使用GRANT語句為用戶賬戶和角色分配權限或角色：GRANT權限類型[(列名列表)][,權限類型[(列名列表)]]...ON[對象類型]權限級別TO用戶或角色[,用戶或角色]...[WITHGRANTOPTION]GRANTPROXYON用戶或角色TO用戶或角色[,用戶或角色]...[WITHGRANTOPTION]GRANT角色[,角色]...TO用戶或角色[,用戶或角色]...[WITHADMINOPTION]對象類型：{TABLE|FUNCTION|PROCEDURE}權限級別：{*|*.*|數(shù)據(jù)庫名.*|數(shù)據(jù)庫名.表名|表名|數(shù)據(jù)庫名.例程名}用戶或角色：{用戶|角色}用戶：'用戶名'@'主機名'角色：'角色名'@'主機名’9.2