0

I'm developing a php/MySQLi based site. I have a question about what might happen if the page for some reason crashes in the middle of executing a complex procedure.

Example: 

if (get_message_call)
{
    add something to table 1
    add something to table 2
    add something to table 3   ( based on some values inserted into table2 ) 
    add something to table 4
    etc...
}

I've built the database using PHPmyAdmin (InnoDB). I haven't assigned any relations though since primary/foreign keys didn't work out as in theory (perhaps i dit it wrong).

My main concern is that if the statement for some reason gets aborted (page crash, loose connection etc) in the middle of lets say executing the "add something to table3". I guess that the first and second statement will get executed, and the rest wont?

What is the best way to ensure that this wont be possible to occur since it might mess up the keys etc for the other tables.. i might be a bit fuzzy with the explenation but i hope someone understands my point.

I've read about "Rollbacks and Commits in Stored Procedures and Triggers" but i'm not sure if i understood it right..

Thanks in advance.

Markus
  • 616
  • 1
  • 9
  • 24

2 Answers2

1

If it's InnoDB, then you should be using transactions for this kind of inserts. For more info see:

Zathrus Writer
  • 4,311
  • 5
  • 27
  • 50
0

This is a case where you should use a database transaction. When you have to update multiple tables, and the updates depend on previous updates, it is safest to put the code into a stored procedure on the database. The stored procedure can start a transaction, which will commit the data to the database only if it finishes correctly (it means that if you write to one table, write to a second table, but the third write fails, the first and second writes are also rolled back).

Here is an example - the stored procedure takes 3 input arguments, returns 2 output values and updates 3 tables. If there is any error, the transaction rolls back. (Note this is just an example, there may be minor syntax errors)

CREATE DEFINER=`root`@`localhost` PROCEDURE `doSomething`(
  IN input1 INT,
  IN input2 VARCHAR(255),
  IN input3 VARCHAR(100),
  OUT output1 INT,
  OUT output2 INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END;
DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; END;
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ROLLBACK; END;

START TRANSACTION;

INSERT INTO table1 (column1, column2, column3) VALUES (input1, input2, NOW());
SET @new_row_id = LAST_INSERT_ID();


INSERT INTO table2 (column1, colum2) VALUES (@new_row_id, input3);
SET @other_new_row_id = LAST_INSERT_ID();

UPDATE table3 SET mycolumn = @other_new_row_id WHERE id = @new_row_id;

COMMIT;

SET output1 = @new_row_id;
SET output2 = @other_new_row_id;

END
GarethL
  • 1,473
  • 1
  • 15
  • 16
  • Ok, doesn't seem to be that complicated. Now i'm a bit more relieved. Thank you for your time. – Markus Oct 10 '12 at 10:52