MySQL 5第十二次學科練習

這是第十二次ㄚ琪自我練習的結果,測試50題,答對46題得92分,錯4題扣4分,總計得分88分,這應該還是有A等級,這幾次的測試都有在A,已經讓ㄚ琪的信心大增,並著手開始歸劃下一個項目的認證,考量到公家機關、國營事業及深入淺出的書籍搭配,ㄚ琪又再度拿起以前擅長的C了,又是一段時間沒有磨練了,生鏽了不少。好了,閒話太多了,回到主題吧,這一回錯的題目就比較是沒見過的了,讓我們研究研究吧。

2013-07-17_084943

這是MySQL 5實力養成暨評量裡的4-11.『整數型數值型態中的BIGINT需佔多少Bytes的空間?』

答案:(D)8

這一提的癥結點在於我們實在記不起來各種資料型態的範圍,我們已經在MySQL 數值型態列過了這些資訊,無奈我們記不起來。我想我們應該更有效綠的去記吧,像是1 byte是TINYINT、SMALLINT就是再double、INT是double的double,而BIGINT就是INT的double了,這樣或許會比較好記。

2013-07-17_085014

這是MySQL 5實力養成暨評量裡的8-53.『下列何項指令可解除鎖定資料表emp?』

答案:(C)UNLOCK TABLES

這一題跟交易有關,不過我們沒有很多經驗,正如今天有個新來的總務,把很多事給搞砸了一樣,馬上就被冠上天字頭的稱號,不過這種事應該多練習就好了吧,我們來看看手冊練習一下吧,MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.3 MySQL Transactional and Locking Statements :: 13.3.5 LOCK TABLES and UNLOCK TABLES Syntax 或 MySQL 5.1参考手册 :: 13. SQL语句语法::13.4. MySQL事务处理和锁定语句::13.4.5. 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時,或當與伺服器的連接被關閉時,所有由當前線程鎖定的資料表被隱含地解鎖。

資料表鎖定只用於防止其它客戶端進行不正當地讀取和寫入。保持鎖定(即使是讀取鎖定)的客戶端可以進行資料表層級的操作,比如DROP TABLE。

注意,下面是對交易資料表使用LOCK TABLES的說明:

·         在嘗試鎖定資料表之前,LOCK TABLES不是交易安全型的,會隱含地提交所有活性交易。同時,開始一項交易(例如,使用START TRANSACTION),會隱含地執行UNLOCK TABLES。(見13.4.3節,「會造成隱式提交的語句」

·         對交易資料表(如InnoDB)使用LOCK TABLES的正確方法是,設置AUTOCOMMIT=0並且不能使用UNLOCK TABLES,直到您明確地提交交易為止。當您使用LOCK TABLES時,InnoDB會內部地取其自己的資料表鎖定,MySQL取其自己的資料表鎖定。InnoDB在下一個提交時釋放其資料表鎖定,但是,對於MySQL,要釋放資料表鎖定,您必須使用UNLOCK TABLES。您不應該讓AUTOCOMMIT=1,因為那樣的話,InnoDB會在使用LOCK TABLES之後立刻釋放資料表鎖定,並且很容易形成死鎖定。注意,如果AUTOCOMMIT=1,我們根本不能獲取InnoDB資料表鎖定,這樣就可以幫助舊的應用軟件避免不必要的死鎖定。

·         ROLLBACK不會釋放MySQL的非交易資料表鎖定。

要使用LOCK TABLES,您必須擁有相關資料表的LOCK TABLES權限和SELECT權限。

使用LOCK TABLES的主要原因是倣傚交易,或在更新資料表時加快速度。這將在後面進行更詳細的解釋。

如果一個線程獲得對一個資料表地READ鎖定,該線程(和所有其它線程)只能從該資料表中讀取。如果一個線程獲得對一個資料表的WRITE鎖定,只有保持鎖定的線程可以對資料表進行寫入。其它的線程被阻止,直到鎖定被釋放時為止。

READ LOCAL和READ之間的區別是,READ LOCAL允許在鎖定被保持時,執行非衝突性INSERT語句(同時插入)。但是,如果您正打算在MySQL外面操作資料庫檔案,同時您保持鎖定,則不能使用READ LOCAL。對於InnoDB資料表,READ LOCAL與READ相同。

當您使用LOCK TABLES時,您必須鎖定您打算在查詢中使用的所有的資料表。雖然使用LOCK TABLES語句獲得的鎖定仍然有效,但是您不能訪問沒有被此語句鎖定的任何的資料表。同時,您不能在一次查詢中多次使用一個已鎖定的資料表——使用別名代替,在此情況下,您必須分別獲得對每個別名的鎖定。

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

如果您的查詢使用一個別名引用一個資料表,那麼您必須使用同樣的別名鎖定該資料表。如果沒有指定別名,則不會鎖定該資料表。

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

相反的,如果您使用一個別名鎖定一個資料表,您必須使用該別名在您的查詢中引用該資料表。

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

WRITE鎖定通常比READ鎖定擁有更高的優先權,以確保更新被盡快地處理。這意味著,如果一個線程獲得了一個READ鎖定,則另一個線程會申請一個WRITE鎖定,後續的READ鎖定申請會等待,直到WRITE線程獲得鎖定並釋放鎖定。您可以使用LOW_PRIORITY WRITE鎖定來允許其它線程在該線程正在等待WRITE鎖定時獲得READ鎖定。只有當您確定最終將有一個時機,此時沒有線程擁有READ鎖定時,您才應該使用LOW_PRIORITY WRITE鎖定。

LOCK TABLES按照如下方式執行:

1.    按照內部定義的順序,對所有要被鎖定的資料表進行分類。從用戶的角度,此順序是未經定義的。

2.    如果使用一個讀取和一個寫入鎖定對一個資料表進行鎖定,則把寫入鎖定放在讀取鎖定之前。

3.    一次鎖定一個資料表,直到線程得到所有鎖定為止。

該規則確保資料表鎖定不會出現死鎖定。但是,對於該規則,您需要注意其它的事情:

如果您正在對一個資料表使用一個LOW_PRIORITY WRITE鎖定,這只意味著,MySQL等待特定的鎖定,直到沒有申請READ鎖定的線程時為止。當線程已經獲得WRITE鎖定,並正在等待得到鎖定資料表清單中的用於下一個資料表的鎖定時,所有其它線程會等待WRITE鎖定被釋放。如果這成為對於應用程式的嚴重的問題,則您應該考慮把部分資料表轉化為交易安全型資料表。

您可以安全地使用KILL來結束一個正在等待資料表鎖定的線程。請參見13.5.5.3節,「KILL語法」

注意,您不能使用INSERT DELAYED鎖定任何您正在使用的資料表,因為,在這種情況下,INSERT由另一個線程執行。

通常,您不需要鎖定資料表,因為所有的單個UPDATE語句都是原子性的;沒有其它的線程可以干擾任何其它當前正在執行的SQL語句。但是,在幾種情況下,鎖定資料表會有好處:

·         如果您正在對一組MyISAM資料表運行許多操作,鎖定您正在使用的資料表,可以快很多。鎖定MyISAM資料表可以加快插入、更新或刪除的速度。不利方面是,沒有線程可以更新一個用READ鎖定的資料表(包括保持鎖定的資料表),也沒有線程可以訪問用WRITE鎖定的資料表(除了保持鎖定的資料表以外)。

有些MyISAM操作在LOCK TABLES之下更快的原因是,MySQL不會清空用於已鎖定資料表的關鍵緩存,直到UNLOCK TABLE被使用為止。通常,關鍵緩存在每個SQL語句之後被清空。

·         如果您正在使用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。請參見1.8.5.3節,「事務和原子操作」

通過使用用戶層級的顧問式鎖定函數GET_LOCK()和RELEASE_LOCK(),您也可以在有些情況下避免鎖定資料表。這些鎖定被保存在伺服器中的一個混編資料表中,使用pthread_mutex_lock() 和pthread_mutex_unlock(),以加快速度。請參見12.9.4節,「其他函數」

要瞭解更多有關鎖定規則的說明,請參見7.3.1節,「鎖定方法」

您可以使用FLUSH TABLES WITH READ LOCK語句鎖定位於所有帶有讀取鎖定的資料庫中的所有資料表。請參見13.5.5.2節,「FLUSH語法」。如果您有一個可以及時拍攝快照的檔案系統,比如Veritas,這是獲得備份的一個非常方便的方式。

註釋:如果您對一個已鎖定的資料表使用ALTER TABLE,該資料表可能會解鎖。請參見A.7.1節,「與ALTER TABLE有關的問題」

基本上沒事不會去LOCK有的沒的,但是一旦要解鎖,就通通UNLOCK吧。

2013-07-17_085056

這是MySQL 5實力養成暨評量裡的8-29.『請參閱附圖作答

自訂函式如下:

CREATE FUNCTION func1() RETURNS INT

BEGIN

DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO

ST v1 = v1 -1;

END WHILE;

RETURN v1;

END;

如使用者執行select func1();請問所得數值為何?

答案:(C) 0

這一題再回來看時,答案真的是0,但是為何就是會選1呢?真是詭異,所以我們不另立一篇來討論。

2013-07-17_085142

這是MySQL 5實力養成暨評量裡的4-63.『在一個表格中,某些欄位的值是不能重複的,例如:帳號,通常我們會把這種欄位設成下列何項?』

答案:(C) UNIQUE

這一題似乎也要好好討論一下,但是仔細一看又似乎是不小心答錯的,所以我們不討論了,只是要好好檢討不要太粗心。

感謝你看到這裡,很快就可以離開了,但最好的獎勵行動就是按一下幫我分享或留言,感恩喔~

點我分享到Facebook

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *