StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>MySQL does support transactions, but not for MyISAM, you'll have to use InnoDB or likewise engine if you want transactions.</p> <p>There is a trick you can use and still keep MyISAM and have transactions (kind of).<br> <strong>Here's how to have your cake and eat it too :-).</strong></p> <p><strong>Step 1</strong><br> Create a blackhole table</p> <pre><code>CREATE TABLE bh_insert_tables t1.pk integer, t2field1 varchar(45), t2field2 integer, .... t3field1 integer, .... etc for all tables ) ENGINE = BLACKHOLE; </code></pre> <p><strong>Step 2</strong><br> Create a memory table to store the dummy transactions into </p> <pre><code>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; </code></pre> <p><strong>Step 3</strong><br> Put a trigger on the blackhole table that will fire on insert.<br> This trigger will also add transaction support (kind of) to MyISAM. </p> <pre><code>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 ; </code></pre> <p>In your insertion routine you can query table <code>status</code> to see which records from table1 in MSSQL where inserted successfully and which ones failed.</p> <p><strong>Links:</strong><br> <a href="http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html</a><br> <a href="http://dev.mysql.com/doc/refman/5.0/en/triggers.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/triggers.html</a><br> <a href="http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html</a><br> <a href="http://dev.mysql.com/doc/refman/5.5/en/delete.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en/delete.html</a><br> <a href="http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload