mysql - 数据库数据完整性问题

Question

我需要制作一个 Windows 服务(每 5 分钟处理一次数据)

1. 从表 1(队列表)(MSSQL)中获取每条记录

2. 将该记录插入各种表 表 2、3、4、5 (MySQL)(带有引擎 MyISAM)

3. 从表 1 和表 2 中插入数据进入表3(映射表)(MSSQL)

4. 再次从表1中删除记录。

这怎么能有效地完成，因为 MySQL 似乎不支持事务。我可以使用 TransactionScope 吗？或者如果下一步出错，我是否应该手动删除上一步创建的记录。我正在使用 MySQL Connector 和 Linq2SQL。任何建议都会有所帮助。谢谢。

Answer 1

MySQL 确实支持事务，但不支持 MyISAM，如果您需要事务，则必须使用 InnoDB 或类似的引擎。

有一个技巧，您可以使用它并仍然保留 MyISAM 并进行交易(某种程度上)。
这是吃蛋糕和吃蛋糕的方法:-)。

第一步
创建黑洞表

CREATE TABLE bh_insert_tables 
   t1.pk integer,
   t2field1 varchar(45),
   t2field2 integer,
   ....
   t3field1 integer,
   .... etc for all tables
   ) ENGINE = BLACKHOLE;

第 2 步
创建一个内存表以将虚拟交易存储到

CREATE TABLE my_rollback
   id unsigned integer auto_increment primary key,
   last_insert integer not null,
   tablename varchar(15) not null,
   index last_insert using hash ('last_insert'),
   index tablename using hash ('tablename') 
) ENGINE = MEMORY;

CREATE TABLE status
  id unsigned integer auto_increment primary key,
  insert_id integer not null,
  success boolean not null,
  index insert_id using hash ('insert_id')
) ENGINE = MEMORY;

第 3 步
在将在插入时触发的黑洞表上放置一个触发器。
此触发器还将向 MyISAM 添加事务支持(某种程度上)。

DELIMITER $$

CREATE TRIGGER ai_bh_insert_each AFTER INSERT ON bh_insert FOR EACH ROW
BEGIN
  DECLARE table1ID integer;
  DECLARE error boolean;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
     SET error = TRUE;
  END;

  SET error = FALSE;

  INSERT INTO table2 (field1, field2, field3) VALUES (NEW.t2field1, NEW.t2field2, NEW.t2field3);
  IF not(error) THEN BEGIN  
    INSERT INTO my_rollback (last_insert, tablename) VALUES (LAST_INSERT_ID(), 'table2');
    INSERT INTO table3 (field1,field2) VALUES (NEW.t3field1, NEW.t3field2);
  END; END IF;
  IF NOT(error) THEN BEGIN
    INSERT INTO my_rollback (last_insert, tablename) VALUES (LAST_INSERT_ID(), 'table3');
    INSERT INTO table4 .......
  END; END IF;
  IF error THEN BEGIN  /*do_rollback*/ 
    DELETE table2 FROM table2
    INNER JOIN my_rollback ON table2.id = my_rollback.last_insert
    WHERE my_rollback.tabelname = 'table2';

    DELETE table2 FROM table2 .......
    INSERT INTO status (insert_id, success) VALUES (NEW.pk, false);
  END; 
  ELSE BEGIN
    INSERT INTO status (insert_id, success) VALUES (NEW.pk, true);
  END; END IF;
  /*Clear my_rollback for the next insert*/
  DELETE FROM my_rollback WHERE id IS NOT NULL;
END $$

DELIMITER ;

在您的插入例程中，您可以查询表 status 以查看 MSSQL 中 table1 中的哪些记录插入成功，哪些失败。

链接:
http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html
http://dev.mysql.com/doc/refman/5.0/en/triggers.html
http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html
http://dev.mysql.com/doc/refman/5.5/en/delete.html
http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html