Home » 2015 » September (Page 3)

從頭學一次 Isolation level 的 REPEATABLE-READ 與 SERIALIZABLE

在「測試 MariaDB 上 Galera Cluster 的 Isolation」這篇提到了 StripeKyle Kingsbury 寫了「Call me Maybe: MariaDB Galera Cluster」這篇文章,在討論 Galera Cluster 的 Isolation 問題。

Percona 的 CTO Vadim Tkachenko 寫了回應的文章:「Clarification on “Call me Maybe: MariaDB Galera Cluster”」。

看完後再跑去翻了一些資料,首先是 SQL 92 對 Isolation 的原始定義,尤其是對 REPEATABLE-READ 的定義。

REPEATABLE-READ 是透過 P2 ("Non-repeatable read") 來定義的,只要保證不會發生 P2 就是 REPEATABLE-READ 了:

P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

也就是說,T1 讀過的 row 只要在 T1 transaction 的期間內都保持一樣就可以。由於沒有多餘其他保證,所以有機會產生 P3 ("Phantom") 問題:

P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some . SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.


The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

所以依照定義,SERIALIZABLE 一定可以避免 P3 發生,但避免了 P3 不代表做到 SERIALIZABLE

另外一份資料是「MySQL :: MySQL 5.6 Reference Manual :: 13.3.6 SET TRANSACTION Syntax」對於 MySQL 5.6 中 InnoDB 實作 REPEATABLE-READ 的細節:

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section, “Consistent Nonlocking Reads”.

InnoDB 的 REPEATABLE-READ 會在第一次讀取時建立一份 snapshot,所以 MySQL 的 REPEATABLE-READ 有達到不產生 P2 的要求,但也因為 snapshot 的關係而不會產生 P3,不過也就僅此而已,寫入的部份還是沒有達到 SERIALIZABLE 的要求。

回到原來的文章所整理出來的測試資料 (我稍微排版過),在這兩個不同的 transaction 同時發生時:(不同人都轉帳給 id = 8)

BEGIN; -- T1
SELECT balance FROM accounts WHERE account_id = 5;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = 75 WHERE account_id = 5;
UPDATE account SET balance = 125 WHERE account_id = 8;
BEGIN; -- T2
SELECT balance FROM accounts WHERE account_id = 6;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = 80 WHERE account_id = 6;
UPDATE account SET balance = 120 WHERE account_id = 8;

SERIALIZABLE 保證執行結果會與某種循序的結果相符 (可能是 T1 -> T2,也可能是 T2 -> T1)。但 REPEATABLE-READ 只保證在 transaction 裡面看到的 row 不會變化。

在 MySQL 裡,如果你想要跟 MySQL 告知「變更的結果會收到某些欄位的值的影響」,你至少要用 LOCK IN SHARE MODE 來做 (或是用 FOR UPDATE 會更清楚表示出意思),也就是:

BEGIN; -- T1
SELECT balance FROM accounts WHERE account_id = 5 LOCK IN SHARE MODE;
SELECT balance FROM accounts WHERE account_id = 8 LOCK IN SHARE MODE;
UPDATE account SET balance = 75 WHERE account_id = 5;
UPDATE account SET balance = 125 WHERE account_id = 8;
BEGIN; -- T2
SELECT balance FROM accounts WHERE account_id = 6 LOCK IN SHARE MODE;
SELECT balance FROM accounts WHERE account_id = 8 LOCK IN SHARE MODE;
UPDATE account SET balance = 80 WHERE account_id = 6;
UPDATE account SET balance = 120 WHERE account_id = 8;

這樣至少會有一個 transaction 會出現 deadlock 訊息,於是就會確保是正確的結果:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

值得一提的是,Percona 文章提到的第二種解法是有問題的:

SELECT balance FROM accounts WHERE account_id = 5;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = balance - 25 WHERE account_id = 5;
UPDATE account SET balance = balance + 25 WHERE account_id = 8;

這樣「總和」會因為保證原子性 (atomicity) 所以不會產生問題,但 balance 可能會產生出負數。

所以結論是 Kyle Kingsbury 那篇文章測試的方法本來就有問題,在 SELECT 時少了 LOCK IN SHARE MODE 或是 FOR UPDATE 的要求。

第九巡迴上訴法院:DMCA takedown notification 必須先確認是否為合理使用 (Fair Use)

出自 EFF 的「Takedown Senders Must Consider Fair Use, Ninth Circuit Rules」這篇,案件可以參考「Lenz v. Universal Music Corp.」這篇,或是 EFF 整理的「Lenz v. Universal」這篇,由 EFF 發起訴訟控告環球侵犯合理使用權:

The Electronic Frontier Foundation (EFF) filed suit against Universal Music Publishing Group (UMPG) asking a federal court to protect the fair use and free speech rights of a mother who posted a short video of her toddler son dancing to a Prince song on the Internet.

起因在於 Stephanie Lenz 上傳了一段 29 秒的影片,背景有 Let's Go Crazy 這首歌的音樂,而被環球發 DMCA takedown notification 下架:

Stephanie Lenz's 29-second recording shows her son bouncing along to the Prince song "Let's Go Crazy " which is heard playing in the background. Lenz uploaded the home video to YouTube in February to share it with her family and friends.

後來 Stephanie Lenz 發出 counter notification 並且控告環球濫用 DMCA notification:

In late June 2007, Lenz sent YouTube a counter-notification, claiming fair use and requesting the video be reposted. Six weeks later, YouTube reposted the video. In July 2007, Lenz sued Universal for misrepresentation under the DMCA and sought a declaration from the court that her use of the copyrighted song was non-infringing. According to the DMCA 17 U.S.C. § 512(c)(3)(A)(v), the copyright holder must consider whether use of the material was allowed by the copyright owner or the law.

而環球直接挑明不在意 fair use:

In September 2007, Prince released statements that he intended to "reclaim his art on the internet." In October 2007, Universal released a statement amounting to the fact that Prince and Universal intended to remove all user-generated content involving Prince from the internet as a matter of principle.

於是雙方就從 2007 年開始一路打官司,首先的判決是地方法院認為 DMCA takedown 必須確認侵權事實才能發,這包括了要確認 fair use:

The district court held that copyright owners must consider fair use before issuing DMCA takedown notices. Thus, the district court denied Universal's motion to dismiss Lenz's claims, and declined to dismiss Lenz's misrepresentation claim as a matter of law.

同時認為環球濫用 DMCA takedown notification:

The district court believed that Universal's concerns over the burden of considering fair use were overstated, as mere good faith consideration of fair use, not necessarily an in-depth investigation, is sufficient defense against misrepresentation. The court also explained that liability for misrepresentation is crucial in an important part of the balance in the DMCA.

然後就是一路往上打,打到前幾天第九巡迴上訴法院宣佈維持原來判決定案。這是官方放出的 PDF:「UNITED STATES COURT OF APPEALSFOR THE NINTH CIRCUIT (PDF)」。Summary 的部份提到這次判決的結論:

The panel held that the DMCA requires copyright holders to consider fair use before sending a takedown notification, and that failure to do so raises a triable issue as to whether the copyright holder formed a subjective good faith belief that the use was not authorized by law.

這個判決使得目前使用機器自動無條件送 takedown notification 的程式也會受到規範,後續看 EFF 怎麼出招了...

Amazon S3 推出新的種類:Standard - Infrequent Access Storage

Amazon S3 推出了新的種類:「AWS Storage Update – New Lower Cost S3 Storage Option & Glacier Price Reduction」。

原先只有「Standard」、「Reduced Redundancy Storage」以及「Glacier Storage」三種,現在多了一種「Standard - Infrequent Access Storage」。


$0.0125 / gigabyte / month (one and one-quarter US pennies), with a 30 day minimum storage duration for billing, and a $0.01 / gigabyte charge for retrieval (in addition to the usual data transfer and request charges).

拉資料的費用相當的貴啊一個月拉兩次就超過 Standard Storage 的價錢了,所以這個「Infrequent」的要求其實頗嚴苛的...

另外空間的部份以 128KB 為最小單位在計價的:

Further, for billing purposes, objects that are smaller than 128 kilobytes are charged for 128 kilobytes of storage.

所以綜合起來看,Infrequent Access Storage 的設計上是拿來堆資料備份,但希望拉資料時很快就可以拉出來。其實就是 Google Cloud StorageNearline 的想法,一樣有存取另外收費的項目。不過 Nearline 有說平均的 latency 是三秒:

但 IA 好像是跟 Standard 相同等級?至少文章裡沒有提到...

nginx 的 HTTP/2

在「Announcing NGINX Plus R7」這邊 nginx 透漏了目前 HTTP/2 的進度。

NGINX Plus 是商業版本,這次將釋出 HTTP/2 功能:

NGINX Plus now provides a fully supported implementation of the new HTTP/2 web standard. NGINX Plus can be deployed as a front-end HTTP/2 gateway and accelerator for both new and existing web services.

而 open source 版本也將會在 NGINX Plus R7 版釋出後放出:

Based on user testing from the alpha-level patch, and with the early support from corporate co-sponsors Automattic and Dropbox, the final open source version of HTTP/2 will become available following the release of R7.

如同之前提到的,nginx 的實作上會將 HTTP/2 與 SPDY 分開,所以 package 是分開的:

HTTP/2 support is available in the optional nginx‑plus‑http2 package only. The nginx‑plus and nginx‑plus‑extras packages provide SPDY support and are currently recommended for production sites because of wider browser support and code maturity.

至於 open source 版本會怎麼規劃就等看看了...

在 iOS 9 裡安裝 Crystal 擋掉全版廣告

蘋果的 iOS 9 在今天放出來了,更新完以後可以用 Content Blocking 擋廣告,剛剛測過可以擋下全頁式的廣告。

這篇要介紹了的是「Crystal」這個目前限時免費的 app,你可以在「Crystal - Block Ads, Browse Faster.」這邊下載安裝。

iOS 9 的 Content Blocking 功能必須要應用程式支援,而目前只有 Safari 有支援,所以以下的測試是用 Safari 打開行動版的 Facebook (https://m.facebook.com/) 測試的,就拿這篇先來測試:(這張圖片是後來抓的,所以時間是 06:20)

直接打開會先出現全版廣告 (第一張圖),關掉後還會有大量的廣告 (第二張圖):

接著我們打開 Crystal,可以看到什麼都沒得設,因為這套軟體已經做完了:(這張圖片是剛裝完就裝的,所以是 06:00)

接著到「設定」裡面打開 Safari 的阻擋功能:


在 Cisco Router 上被植入的後門

FireEye 發表了一篇在 Cisco Router 上發現被植入的後門:「SYNful Knock - A Cisco router implant - Part I」。

發現這些被植入的 router 被散佈在四個地區:

Mandiant can confirm the existence of at least 14 such router implants spread across four different countries: Ukraine, Philippines, Mexico, and India.


  • Cisco 1841 router
  • Cisco 2811 router
  • Cisco 3825 router


SYNful Knock is a stealthy modification of the router's firmware image that can be used to maintain persistence within a victim's network. It is customizable and modular in nature and thus can be updated once implanted. Even the presence of the backdoor can be difficult to detect as it uses non-standard packets as a form of pseudo-authentication.

最主要的重點是把記憶體保護機制關閉 (都變成 RW):

The malware forces all TLB Read and Write attributes to be Read-Write (RW). We believe this change is made to support the hooking of IOS functions by loaded modules.

文後也有提到 Cisco 的文章,如何 dump image 分析:「Offline Analysis of IOS Image Integrity」。