9

i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data.

I can use a select to get the old data, an insert to history and finally an update to change data.

can i have all these in a single query in mysql without using stored procedures, triggers, etc.. like using locks etc.. if so give me a small sample.

2
  • 1
    @savaranan : This question is worthy of a +1 because it does present a strong reminder to DBAs and Developers to use transactions and take full advantage of the ACID properties of the database. Jun 30, 2011 at 11:14
  • 2
    @savaranan : For all intents and purposes, Jack supplied the ONLY plausible answer there is. In fact, Jack Douglas took it an additional step and forced an intermittent lock on every row with id=10 for added MVCC protection by doing SELECT ... FOR UPDATE. His answer further accentuates the point Jack and I have been saying all along: an UPDATE and INSERT cannot be, nor ever be, a single query, they can only be a single transaction for the SQL behavior your question proposes. Jul 31, 2011 at 1:15

3 Answers 3

13

To do this without the risk of blocking another user trying to update the same profile at the same time, you need to lock the row in t1 first, then use a transaction (as Rolando points out in the comments to your question) :

start transaction;
select id from t1 where id=10 for update;
insert into t2 select * from t1 where id=10;
update t1 set id = 11 where id=10;
commit;
1
  • This is simply brilliant in further locking down every row with id=10. That should be a +2. All I can give is a +1 !!! Jul 31, 2011 at 1:16
1

I don't believe there is a way to combine all three statements. The closest thing to that doesn't really help you, and that is a SET SELECT. Your best bet is a trigger. Below is a sample of a trigger that I often use to maintain just such an audit trail (constructed with PHP):

$trigger = "-- audit trigger --\nDELIMITER $ \n".
    "DROP TRIGGER IF EXISTS `{$prefix}_Audit_Trigger`$\n".
    "CREATE TRIGGER `{$prefix}_Audit_Trigger` AFTER UPDATE ON `$this->_table_name` FOR EACH ROW BEGIN\n";

foreach ($field_defs as $field_name => $field) {
    if ($field_name != $id_name) {
       $trigger .= "IF (NOT OLD.$field_name <=> NEW.$field_name) THEN \n".'INSERT INTO AUDIT_LOG ('.
                    'Table_Name, Row_ID, Field_Name, Old_Value, New_Value, modified_by, DB_User) VALUES'.
                    "\n ('$this->_table_name',OLD.$this->_id_name,'$field_name',OLD.$field_name,NEW.$field_name,".
                    "NEW.modified_by, USER()); END IF;\n";
    }
}
$trigger .= 'END$'."\n".'DELIMITER ;';
-3

I have found that this query works across SQL & MySQL Servers INSERT INTO t2 SELECT * FROM t1 WHERE id=10; UPDATE t1 SET id=11 WHERE id=10;

Hope this is useful to some else also in future.

9
  • 4
    This is not really a query. This is actually two queries which should be treated as a transaction. Jun 30, 2011 at 2:42
  • @rolandomysqldba: this works fine as a single query when i send to a db server from the application code, where i treat this set as a single query. why do you say so?. can you disprove this with strong reasons..
    – Saravanan
    Jun 30, 2011 at 9:25
  • 2
    @saravanan : In the eyes of InnoDB or any ACID-compliant RDBMS (Oracle,SQLServer,PostreSQL,Sybase,etc.), it is impossible to call those two SQL statements one query. As an ACID-compliant database would treat them as two statements. By default, InnoDB has autocommit turned ON. The first statement, INSERT, would execute as a single transaction. Multiversioning Concurrency Control (MVCC) data would be generated to keep a copy of the original data in the t2 table on a row-by-row basis. If MySQL crashes during the execution of the INSERT, InnoDB uses MVCC data to rollback t2 to its original state. Jun 30, 2011 at 10:40
  • 1
    @saravanan : Suppose the INSERT successfully worked. The data that resulted from the INSERT has been committed (with autocommit ON) and the MVCC protecting table t2 is discarded. When you perform the UPDATE, MVCC is generated against the t1 table and the UPDATE is performed. If MySQL crashes during the UPDATE, InnoDB uses MVCC data on t1 to rollback the UPDATE. Even if the UPDATE changing just one row, the one-in-a-milloion possibility exists of moving records from t1 into t2 with id 10 and not changing id 10 to id 11 in t1. To prevent this unique scenario, you need to do the following... Jun 30, 2011 at 10:50
  • @savaranan : Treat the two SQL statements as a single transaction. Simple way way to do this is : BEGIN; INSERT INTO t2 SELECT * FROM t1 WHERE id=10; UPDATE t1 SET id=11 WHERE id=10; COMMIT; The strongest reason for treating the two SQL statements as a single transaction is the fact that the MVCC created for the INSERT would remain in existence during the UPDATE. If a MySQL crash should occur during the UPDATE inside a transaction (BEGIN; ... COMMIT; block) MVCC would rollback all changes to a consistent state. If both INSERT and UPDATE are completed, then MVCC is discarded at the last moment. Jun 30, 2011 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.