關於資料庫 System lock 狀態的說明和處理方式

關於資料庫 System lock 狀態的說明和處理方式
@2010-08-24 for&everA、對於MySQL來說：

 

執行命令show processlist ，可以看到當前的MySQL進程中有好多System lock的狀態。

查詢MySQL手冊：
System lock

The thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).

因此，如果不是幾個 mysqld 服務共用一個表檔案，可以通過設定參數--skip-external-locking 來禁止系統鎖定

B、對於 InforBright來說：

有如下的說法：

IB has only table-level locks. When doing any write operation to a table an exlusive lock is set on it and any other queries needing this table must wait until the writing transaction commits. And vice versa - if a table is used for reading, loading must wait.

I can only suggest, that instead of inserts you can use LOAD command - it runs much faster than inserts. It maybe easier then to find a window between reports to run a load successfully.

You can also use two symmetrical databases. Reporting from one while loading to another. Surely, each load must be done twice then, so thye databases are eventually identical.

也就是說：
InfoBright 會優先讀，因此在LoadData的時候（也就是寫資料的時候）會被鎖定。
那麼，如果當前出現了鎖定狀態，造成寫資料的進程始終無法完成，怎麼辦呢？
很簡單，在寫資料的時候，只要暫停一下讀寫，等寫完資料再讀取即可。

@2010-8-24 forandever