Infobright的事务处理实现与Oracle的类似，但默认是自动提交的,可设置不自动提交事务，默认是在会话级的。设置不自动提交事务以后，本会话可以脏读，其他会话则只能查询到最后一次提交时的内容。如果多个会话对同一个表执行dml操作，后发出的命令被挂起，等候前一个会话提交或回滚才能执行。大部分和Oracle的表现一致，就不一一举例了。
企业版和社区版最显著的区别在于前者支持brighthouse存储引擎的DML操作，这就意味着其他存储引擎的表数据无法在数据库内部转移到brighthouse存储引擎的表中，反之则不然。但对一个面向数据仓库应用的数据库，毕竟主要业务是查询而不是数据修改，问题也不大。实在需要DML操作可以用导入导出迂回进行，insert操作的替代方法如下。
mysql> show create table t; /*t表是BRIGHTHOUSE引擎，无法执行DML操作*/
+-------+--------------------
| t | CREATE TABLE `t` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
`c4` int(11) DEFAULT NULL,
`v` bigint(20) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
1 row in set (0.00 sec)
mysql> call insert_1K(); /*insert操作提示出错*/
ERROR 1031 (HY000): Table storage engine for 't' doesn't have this option
mysql> create table tisam engine=myisam as select * from t ;/*根据表结构创建MyISAM临时表*/
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> call insert_1k_isam(); /*将数据插入临时表*/
Query OK, 1 row affected (0.03 sec)
mysql> select count(*)from tisam;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
mysql> select * from tisam into outfile 'd:/app/tisam.csv'; /*将临时表数据导出为文件*/
Query OK, 1000 rows affected (0.00 sec)
mysql> load data infile 'd:/app/tisam.csv' into table t; /*默认分隔符是','*/
ERROR 2 (HY000): Wrong data or column definition. Row: 1, field: 1.
mysql> load data infile 'd:/app/tisam.csv' into table t fields terminated by '\t';
Query OK, 1000 rows affected (0.36 sec)
Records: 1000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
+-------+--------------------
| t | CREATE TABLE `t` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
`c4` int(11) DEFAULT NULL,
`v` bigint(20) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
1 row in set (0.00 sec)
mysql> call insert_1K(); /*insert操作提示出错*/
ERROR 1031 (HY000): Table storage engine for 't' doesn't have this option
mysql> create table tisam engine=myisam as select * from t ;/*根据表结构创建MyISAM临时表*/
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> call insert_1k_isam(); /*将数据插入临时表*/
Query OK, 1 row affected (0.03 sec)
mysql> select count(*)from tisam;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
mysql> select * from tisam into outfile 'd:/app/tisam.csv'; /*将临时表数据导出为文件*/
Query OK, 1000 rows affected (0.00 sec)
mysql> load data infile 'd:/app/tisam.csv' into table t; /*默认分隔符是','*/
ERROR 2 (HY000): Wrong data or column definition. Row: 1, field: 1.
mysql> load data infile 'd:/app/tisam.csv' into table t fields terminated by '\t';
Query OK, 1000 rows affected (0.36 sec)
Records: 1000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
令人费解的，加载文件的默认分隔符是',',与导出文件的默认分隔符'\t'不一致，而企业版却是一致的,都是'\t'。由于truncate table 操作也不支持，delete和update操作要通过先drop表，再加载文件的方式进行。
Infobright和原始MySQL的SQL解释、执行引擎不同，因此，不支持包含brighthouse的表和其他数据引擎的表的关联操作，其他原始MySQL的数据引擎之间的操作则不在此限。估计若不涉及brighthouse引擎的查询调用的是原始MySQL的SQL解释、执行引擎。
mysql> select count(t.c1) from t,tisam where t.v=tisam.v;
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable
the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.
mysql> select count(t.c1) from t,t t2 where t.v=t2.v;
+-------------+
| count(t.c1) |
+-------------+
| 1000 |
+-------------+
1 row in set (0.03 sec)
mysql> select count(t.c1) from t join tisam on t.v=tisam.v;
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable
the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.
mysql> select count(t.c1) from tisam t,tisam t2 where t.v=t2.v;
+-------------+
| count(t.c1) |
+-------------+
| 1000 |
+-------------+
1 row in set (0.11 sec)
mysql> create table tmem engine=memory as select c1,sum(v) sumv from t group by c1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select count(*) from tisam t,tmem t2 where t.c1=t2.c1;/*不涉及brighthouse引擎*/
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable
the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.
mysql> select count(t.c1) from t,t t2 where t.v=t2.v;
+-------------+
| count(t.c1) |
+-------------+
| 1000 |
+-------------+
1 row in set (0.03 sec)
mysql> select count(t.c1) from t join tisam on t.v=tisam.v;
ERROR 5 (HY000): The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable
the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.
mysql> select count(t.c1) from tisam t,tisam t2 where t.v=t2.v;
+-------------+
| count(t.c1) |
+-------------+
| 1000 |
+-------------+
1 row in set (0.11 sec)
mysql> create table tmem engine=memory as select c1,sum(v) sumv from t group by c1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select count(*) from tisam t,tmem t2 where t.c1=t2.c1;/*不涉及brighthouse引擎*/
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
社区版还提供3.5 beta版供用户测试，而企业版没有。
三、数据加载和查询性能
为了提供用户在做数据库选型的参考，下面沿用TPC-H 2.8 scale为1的大约1G字节数据来进行较大数据量的测试，先进行数据加载测试，MySQL原始加载工具的性能表现已经可以接受了。
mysql> load data infile 'C:/soft/Tcp-h_windows/lineitem.tbl' into table lineitem fields terminated by '|' lines terminated by '|\r\n';
Query OK, 6001215 rows affected (4 min 34.94 sec)
Query OK, 6001215 rows affected (4 min 34.94 sec)
通过设置BH_DATAFORMAT环境变量，改用infobright的数据加载工具。其中二进制方式需要企业版才能支持。社区版只能采用infobright的数据加载工具加载文本。
mysql> set @BH_DATAFORMAT='binary';/*二进制方式*/
Query OK, 0 rows affected (0.00 sec)
mysql> use tpch
Database changed
mysql> select * from lineitem into outfile 'c:/app/lineitem.bin';
Query OK, 6001215 rows affected (4 min 11.80 sec)
mysql> truncate table lineitem;
Query OK, 0 rows affected (0.03 sec)
mysql> load data infile 'c:/app/lineitem.bin' into table lineitem;
Query OK, 6001215 rows affected (48.83 sec)
mysql> set @BH_DATAFORMAT='txt_variable';/*利用infobright加载文本*/
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table lineitem;
Query OK, 0 rows affected (0.03 sec)
mysql> load data infile 'C:/soft/Tcp-h_windows/lineitem.tbl' into table lineitem fields terminated by '|' lines terminat
ed by '|\r\n';
Query OK, 6001215 rows affected (1 min 35.05 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> use tpch
Database changed
mysql> select * from lineitem into outfile 'c:/app/lineitem.bin';
Query OK, 6001215 rows affected (4 min 11.80 sec)
mysql> truncate table lineitem;
Query OK, 0 rows affected (0.03 sec)
mysql> load data infile 'c:/app/lineitem.bin' into table lineitem;
Query OK, 6001215 rows affected (48.83 sec)
mysql> set @BH_DATAFORMAT='txt_variable';/*利用infobright加载文本*/
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table lineitem;
Query OK, 0 rows affected (0.03 sec)
mysql> load data infile 'C:/soft/Tcp-h_windows/lineitem.tbl' into table lineitem fields terminated by '|' lines terminat
ed by '|\r\n';
Query OK, 6001215 rows affected (1 min 35.05 sec)
