0

I have problem while dealing with copy of database table entry from one file to other .

suppose i have two tables called one.sql , two.sql , now i wana transfer some record from one.sql to the table two.sql, then delete that entry from one.sql ater copying success.

problem : suppose power was gone after i make copy from one to two, the delete record from one wasnt done , here in that case same record will be in both tables. that i dont want. so in this situation how to handle these types of inconsistent on fly.

rags
  • 2,580
  • 19
  • 37
Srinivas Thanneeru
  • 161
  • 1
  • 2
  • 12

2 Answers2

3

Your RDBMS is not a simple datastore! It supports journaling, transaction isolation and atomic updates. So...

... with transactional tables (InnoDB) and with decent isolation level simply do:

START TRANSACTION -- Or SET autocommit = 0
INSERT INTO two SELECT * FROM one WHERE ...;
DELETE FROM one WHERE ...;
COMMIT

COMMIT will atomicity apply the changes to the DB. That is, from the other transactions point of view, the move is either done or not started. No one can see it half done. Even in case of catastrophic failure (power outage).

Of course, if you move all your records, you could also rely on RENAME TABLE...

Community
  • 1
  • 1
Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
  • yes ur right , can u give me some more information about transaction isolation and atomic updates, plz provide some usefull links for me to learn about these. – Srinivas Thanneeru Jul 30 '13 at 10:42
1

You can use transaction blocks to descrease unexpected results at some degree. But solving a power problem is another thing.

You can however use a batch and check if two tables don't contain same records at some interval, if you are worried about a power problem.

Semih Yagcioglu
  • 4,011
  • 1
  • 26
  • 43
  • "But solving a power problem is another thing." That's simply *not* true. MySQL with transactional table (*InnoDB*) is [ACID](http://dev.mysql.com/doc/innodb/1.1/en/glossary.html): That means that "either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back". "The database remains in a consistent state at all times". And "The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, [...]". – Sylvain Leroux Jul 30 '13 at 11:10