以 MySQL 的 InnoDB 為例，預設的 Tansaction isolation level 為 REPEATABLE READ，在 SELECT 的讀取鎖定主要分為二大類:

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

這二種方式在交易 (Transaction) 進行當中 SELECT 到同一個資料表時，都必須等待它方交易資料被送交(Commit)後才會執行。而主要的不同在於 LOCK IN SHARE MODE 在有一方交易要 Update 同一個表單時很容易造成 Dead Lock 。

簡單的說，如果 SELECT 後面若要 UPDATE 同一個表單，最好使用 SELECT ... UPDATE。

舉個例子: 假設商品表單 products 內有一個存放商品數量的 quantity ，在訂單成立之前必須先確定 quantity 商品數量是否足夠 (quantity>0) ，然後才把數量更新為 1。

不安全的做法:

SELECT quantity FROM products WHERE id=3;
UPDATE products SET quantity = 1 WHERE id=3;

為什麼不安全呢?
少量的狀況下或許不會有問題，但是大量的資料存取「鐵定」會出問題。

如果我們需要在 quantity>0 的情況下才能扣庫存，假設程式在第一行 SELECT 讀到的 quantity 是 2 ，看起來數字沒有錯，但是當 MySQL 正準備要 UPDATE 的時候，可能已經有人把庫存扣成 0 了，但是程式卻渾然不知，將錯就錯的 UPDATE 下去了。

因此必須透過的交易機制來確保讀取及送交的資料都是正確的。

於是我們在 MySQL 就可以這樣測試: (註1)

SET AUTOCOMMIT=0;
BEGIN WORK;
SELECT quantity FROM products WHERE id=3 FOR UPDATE;
===========================================
此時 products 資料中 id=3 的資料被鎖住(註3)，其它交易必須等待此次交易
送交後才能執行 SELECT * FROM products WHERE id=3 FOR UPDATE (註2)
如此可以確保 quantity 在別的交易讀到的數字是正確的。
===========================================
UPDATE products SET quantity = '1' WHERE id=3 ;
COMMIT WORK;
===========================================
送交(Commit)寫入資料庫，products 解鎖。

註1: BEGIN/COMMIT 為交易的起始及結束點，可使用二個以上的 MySQL Command 視窗來交互觀察鎖定的狀況。

註2: 在交易進行當中，只有 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE 同一筆資料時會等待其它交易結束後才執行，一般 SELECT ... 則不受此影響。

註3: 由於 InnoDB 預設為 Row-level Lock，資料列的鎖定可參考這篇。

註4: InnoDB 表單儘量不要使用 LOCK TABLES 指令，若情非得已要使用，請先看官方對於 InnoDB 使用 LOCK TABLES 的說明，以免造成系統經常發生 Deadlock。

請問一下，這種語法(SELECT ... FOR UPDATE)是MySQL的專用語法嗎？

tokimeki:

SELECT ... FOR UPDATE 是 MySQL 專用的沒錯。由於 MySQL 早期並沒有 Transaction 的功能，只能靠手動 Lock Table 來處理，後來有了 InnoDB 之後才有這種方便的功能。

恕我肉腳
請問mysql哪一個版本開始支援Transaction？
我都只用到4.x

neo 大大寫的文意真好
我很喜歡看你的 blog
文筆很清晰、而且每一篇的內容也很充實 + 實用

所以~ neo 大大
你可不可再幫我多推薦幾個像你寫的這麼好的 blog

感恩呀 ^________^!!

南庄民宿網:
MySQL 4.x 就已經有包含 InnoDB 囉。

阿飛來也~~:
多謝你誇獎，因為我不知道你喜歡看什麼類型的 Blog ，你可以去 Oui-blog 聯播或 Yam news 逛看看，應該都可以找到不錯的 Blog。

http://news.yam.com/blog/
http://www.oui-blog.com/

另外也可以看看 Bloglines，參考訂閱我的 Blog RSS 的人都在看哪些 Blog:

http://www.bloglines.com/search?t=1&r=0&q=www.neo.com.tw

大大您好
可否請教您一個問題
日前, 接到老板丟給我的新工作..>把一個程式增加些功能
這原來不是件大事, 難過的是...它所使用的欄位名稱有空格
(資料庫為 Mysql)
一般而言, 欄位名稱不是都不能有空白嗎?
如....date_time,而不是 data time
原來是想, 那直接把欄位名稱改過來就好, 但老板說, 不曉得
還有哪些AP用同一個資料庫..所以..名稱不能動
但是, 若我想select某些特定欄位, 語法則不被接受
如
select * from tab_name where date time='XXXXX'

能否請大大幫個忙, 教我如何處理這個困境

感謝

加 ` 就可以了:
select * from tab_name where `date time`='XXXXX'

謝大大的回應, 但這個方法已試過, 忘記告訴大大是我的錯
-----------------------
加 ` 就可以了:
select * from tab_name where `date time`='XXXXX'
-----------------------
它會出現下列訊息
Error 1064
you have an error in your SQL syntax, check the
manual that corresponds to your mysql server
version for the right syntax to use.

而我的Mysql是4.x..不過, 這會有很大的影響嗎

Jackal:
八成你把 ` 當成是單引號 ' ，再試一次絕對可以的啦!

Dear Neo
Really thanks your help, it`s working.

Dear Neo...
看了你這篇文章之後，我試著將我的PHP程式中的Query語法改用你文章中的作法..SELECT..FOR UPDATE去鎖定我的table...
我修改完之後，進行的測試方法如下..
1.在A電腦修改一筆紀錄，模擬修改到一半，沒有作COMMIT動作..
2.去B電腦修改同一筆紀錄，並做COMMIT動作...
結果我發現B電腦還是可以修改同一筆記錄耶...我確定我在做SELECT語法時已經有加入FOR UPDATE，這一筆紀錄原則上應該是已經被LOCK了呀，為何B電腦還是可以修改呢..
可以麻煩Neo大大幫忙一下嗎...

Thanks..
Kevin..

Kevin:
確定資料庫表單是 InnoDB 嗎?

Dear Neo...

是的..我有確定我的Table是InnoDB...我的程式如下..麻煩你看一下囉..感恩..

下面這支程式會先撈出要Update的資料$dept_snid

//檢查有沒有傳入$dept_snid值
if (empty($dept_snid))
die("URL沒有提供$dept_snid值");

//建立資料庫連線-->透過db.inc裡面的$dsn
$connection = & DB::connect($dsn);
if(DB::isError($connection)){die("無法連接資料庫" . getMessage($connection));}

$connection->setFetchMode(DB_FETCHMODE_ASSOC);

//解決寫入或讀出MySQL資料庫亂碼問題
mysql_query("SET NAMES 'big5'");

$stmt = "SELECT * FROM department WHERE `dept_snid`='$dept_snid' FOR UPDATE";
$result =& $connection->getRow($stmt);
if(DB::isError($result))die("無法Query資料庫" . $result->getMessage());

第二支程式會從Form擷取資料，寫回要Update的$dept_snidRecord
//檢查有沒有傳入$dept_snid值
if (empty($dept_snid))
die("URL沒有提供$dept_snid值");

$connection = & DB::connect($dsn);
if (DB::isERROR($connection)){die("無法連接資料庫" . $connection->getMessage());}

//使用POST方式從表單取得部門資料到變數中
//$form_dept_id = $_POST["form_dept_id"];
$form_dept_name = $_POST["form_dept_name"];
$form_dept_manager = $_POST["form_dept_manager"];
$form_dept_parent = $_POST["form_dept_parent"];
$form_dept_mail = $_POST["form_dept_mail"];

//解決寫入或讀出MySQL資料庫亂碼問題
mysql_query("SET NAMES 'big5'");

//SQL Query語法-->將資料寫入MYSQL資料庫中
$stmt = "UPDATE department SET
`dept_name`='$form_dept_name', `dept_manager`='$form_dept_manager', `dept_parent`='$form_dept_parent', `dept_mail`='$form_dept_mail'
WHERE `dept_snid`='$dept_snid'";

$result =& $connection->query($stmt);
if(DB::isError($result))
die("寫入資料庫失敗" . $result->getMessage());

Kevin:
看不出你哪裡有寫 Transaction。

Dear Neo...

我改用ADODB Coding，並且加上StartTrans()及CompleteTrans()，準備要Update的資料還是沒有鎖定耶..
程式如下...

第一支程式--撈出要修改的資料
//檢查有沒有傳入$dept_snid值
if (empty($emp_snid))
die("URL沒有提供$emp_snid值");

//建立資料庫連線-->透過db.inc裡面的$dsn
$db = &NEWADOconnection($dsn);
if(!$db)die("無法連接資料庫");

//設定Fetch mode
$ADODB_FETCH_MODE =ADODB_FETCH_ASSOC;

//解決寫入或讀出MySQL資料庫亂碼問題
mysql_query("SET NAMES 'big5'");

//Start Transation
$db->StartTrans();

//Query出要Update的資料，並使用FOR UPDATE LOCK紀錄
$stmt = "SELECT * FROM employee WHERE `emp_snid`='$emp_snid' FOR UPDATE;";

$result =& $db->GetRow($stmt);
if(!$result)die("無法Query資料庫");

第二支程式--寫入要修改的資料
//檢查有沒有傳入$emp_snid值
if (empty($emp_snid))
die("URL沒有提供$emp_snid值");

$db = & NEWADOconnection($dsn);
if (!$db)die("無法連接資料庫");

//使用POST方式從表單取得部門資料到變數中
//$form_emp_id = $_POST["form_emp_id"];
$form_emp_name = $_POST["form_emp_name"];
$form_emp_deptid = $_POST["form_emp_deptid"];
$form_emp_sex = $_POST["form_emp_sex"];
$form_emp_bhday = $_POST["form_emp_bhday"];
$form_emp_telno = $_POST["form_emp_telno"];
$form_emp_telho = $_POST["form_emp_telho"];
$form_emp_telmo = $_POST["form_emp_telmo"];
$form_emp_faxno = $_POST["form_emp_faxno"];
$form_emp_email = $_POST["form_emp_email"];
$form_emp_city = $_POST["form_emp_city"];
$form_emp_addr = $_POST["form_emp_addr"];

//解決寫入或讀出MySQL資料庫亂碼問題
mysql_query("SET NAMES 'big5'");

//SQL Query語法-->將資料寫入MYSQL資料庫中

$stmt = "UPDATE employee SET
`emp_name`='$form_emp_name', `emp_deptid`='$form_emp_deptid', `emp_sex`='$form_emp_sex',
`emp_bhday`='$form_emp_bhday', `emp_telno`='$form_emp_telno', `emp_telho`='$form_emp_telho',
`emp_telmo`='$form_emp_telmo', `emp_faxno`='$form_emp_faxno', `emp_email`='$form_emp_email',
`emp_city`='$form_emp_city', `emp_addr`='$form_emp_addr'
WHERE `emp_snid`='$emp_snid';";

$result =& $db->Execute($stmt);
if(!$result)
die("寫入資料庫失敗");

//Complete Transation
$db->CompleteTrans();
$db->Close();

Kevin:

看不出 ADODB 的 AutoCommit 有沒有關。

我建議你直接用 MySQL Command Line 開二個視窗測，環境比較單純。

另外別再放程式上來了。我不會再幫你看程式了。

Neo

很高兴看到这样的Blog，学到SQL很多知识

才发现这个是MT的Blog，嘻嘻，功能强大。

請問mssql要做到同樣的功能要怎麼做呢？

在MySql 中提供的lock , 應該是table lock, 也就是在做這個動作時, 會把整張表格都lock 起來, 如果有另一筆交易，要更新的是另一產品的庫存, 也會被鎖住! 所以如果碰到大量的線上交易時, 就會出現延遲的現象!　改採oracle DB，因oracle 提供row lock , 也就是只鎖住select 出來的那幾筆，不會整個table 都不能update , 對大量的線上交易，效能就提升很多！　以前我們使用的經驗，分享一下，如有什麼不錯，請指教!

cnangel:
歡迎你常來喔...^^

rex:

可以參考:
http://203.74.250.204/ui/TechDocs/SQL/Lessons/implement/Locking.htm

Rico:

MySQL InnoDB 預設就是 row lock 喔: ^^
http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

這裡有 InnoDB row-level Lock 中文的實作範例參考:
http://download.dbworld.com.tw/mag_article/s030901903.pdf

感謝Neo提供的資料,真是高手! 我找個機會試一下效能,再跟大家分享!!

Rico:

您太過獎啦，我想這個範例我還是改一下好了，免的容易被誤解。

順便補充說明，FOR UPDATE 的功能，只有被選擇到的資料列才會被 LOCK，並非整個 Table 被 Lock。^^

SET AUTOCOMMIT=0;
BEGIN WORK;
COMMIT WORK;

那要是在php中執行，就是
mysql_query(SET AUTOCOMMIT=0);
mysql_query(BEGIN WORK);
mysql_query(COMMIT WORK);
囉??

Neo 大 我跟 axis 有同樣問題

呵~~解決了
本來一直測不出來
mysql_query("BEGIN",$link);
$sql="SELECT * FROM test_string2 WHERE id=3 FOR UPDATE ";
$rs= mysql_query($sql,$link);
$row=mysql_fetch_array($rs,MYSQL_ASSOC);
print_r($row);
sleep(25);
mysql_query("COMMIT",$link);
不過加了 sleep(25); 就可以發現真的有 LOCK 住~~

[quote]
SELECT ... FOR UPDATE 是 MySQL 專用的沒錯
[/quote]
select ... for update 並非 MySQL 專用. 一般大型資料庫 如 Oracle or Sybase 都有支援.

各位高手前輩實在太強了!
小弟都是來這偷學東西的,感謝您!

free xbox 360[url=http://beacon.edu/includes/?prj/xbox.html]free xbox 360[/url] karaoke machine[url=http://wmco.org/js/index.php?prj/karaoke.html]karaoke machine[/url] yacht charter[url=http://www.parity.com/uploads/wp/?prj/yachts.html]yacht charter[/url]

south beach diet[url=http://www.equinix.fr/plugins/cache/?prj/diet.html]south beach diet[/url] cosmetic surgery facelift[url=http://www.maxiscoot.com/webdav/test?/cosmetic.html]cosmetic surgery facelift[/url] internet advertising[url=http://sustainabilitycoalition.org/plugins/?prj/internet-advertising.html]internet advertising[/url]

internet advertising[url=http://www.parity.com/uploads/wp/?prj/internet-advertising.html]internet advertising[/url] breast cancer[url=http://www.fresca.co.uk/plugins/cache/?prj/breast-cancer.html]breast cancer[/url] breast cancer[url=http://wmco.org/js/index.php?prj/breast-cancer.html]breast cancer[/url]

wine theme wedding[url=http://www.maxiscoot.com/webdav/test?/wine.html]wine theme wedding[/url] sexy lingerie[url=http://www.unitedplantsavers.org/uploads/?prj/lingerie.html]sexy lingerie[/url] anti aging skin care product[url=http://www.keioc.net/plugins/cache/?prj/skincare.html]anti ag