关于数据库 System lock 状态的说明和处理方式

关于数据库 System lock 状态的说明和处理方式
@2010-08-24 for&ever

A、对于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