Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I already know how to use transactions, what I want to know is how MySQL transaction handles data being processed...

For example, consider the following:

    1. I have 2 databases (db1 and db2) and both databases are geographically far from each other, more like cities away.  
    2. db1 has a table namely, tbl_orig  
    3. db2 has a table namely, tbl_backup  
    4. I want to use MySQL transaction when copying the table from db1 (tbl_orig) into db2 (tbl_backup) so that in case of a power outage or anything that would interrupt the process, it would prevent my database to be in an unstable state.  

Say I use the following commands below:

    mysql> start transaction;  
    mysql> insert into db2.tbl_backup select * from db1.tbl_orig;  
    mysql> commit;  

In the above sql statements, in between the "start transaction" and the "commit" lines... what exaclty is happening to the rows indicated in the insert statement?

Does all of the rows affected by the insert statement from db1 gets transfered into db2 immediately and are waiting for the commit line to execute in order to make the insert official?

Or, are the rows affected by the insert statement are still inside db1 and are just waiting to be transfered into db2 once the "commit" command executes?

I wanted to know this facts because I will be copying data from a database through the internet and I'm worried that I might lose some data in the process... Any help would be greaty appreciated.

share|improve this question

migrated from stackoverflow.com Apr 13 '13 at 0:22

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 2 down vote accepted

Does all of the rows affected by the insert statement from db1 gets transfered into db2 immediately and are waiting for the commit line to execute in order to make the insert official?

Transafer is made immediatly, but other sessions will not see inserted data until commit (depends on transaction isolation level)

Or, are the rows affected by the insert statement are still inside db1 and are just waiting to be transfered into db2 once the "commit" command executes?

Data are not waiting for commit, just storage engine wait for commit to make changes persisted.

I wanted to know this facts because I will be copying data from a database through the internet and I'm worried that I might lose some data in the process... Any help would be greaty appreciated.

Every TCP based connection guarantee data integrity.

You should use replication instead of this approach

share|improve this answer
    
I was supposed to do replication since its a lot more easier to do backups in an automated fashion but my boss wanted something different, he wants to make things manual...meaning, a user has to click on a button to initiate the backup processs that's why I opted for the MySQL transaction... I will look into the transaction isolation level now, and try to understand the concept. thnx ^_^ –  chad Apr 13 '13 at 0:06
    
Hey thanks a lot, there are still some more readings I need to do but I think I already get the idea. ^_^ –  chad Apr 13 '13 at 2:51

2 things can happen:

  1. The Data is transferred successfully -> the INSERT is carried out = NO Problem

  2. The Data is not fully transferred -> the INSERT will not be carried out = Still no Problem

A commit can only be carried out if the operations inside the transaction were carried out successfully. The reason for the explicitly required commit is the ability to validate the undertaken action with the possibility of a rollback...

share|improve this answer
    
All of the data are transferred or nothing at all, is that what you mean?... I am concerned of the actual location of the data being processed because I will be transferring about 50,000 rows or more and I am worried that if that some of the data might get lost during the process because of a power outage, or internet connection issues and other factors that may affect a successful backup... –  chad Apr 13 '13 at 0:20
    
The Data can't just get lost along the way, like rkosegi said, the underlying osi layers are taking care of that. in the event of a power outage, the transfer would be incomplete -> the data that was transferred up until then gets trashed. –  M.Bennett Apr 13 '13 at 2:24
    
So with a transaction you're actually setting the rule of all or nothing. Would you simply INSERT to a remote machine/db you would be more likely facing the problems you are anticipating. –  M.Bennett Apr 13 '13 at 2:33
    
Hey thanks a lot, there are still some more readings I need to do but I think I already get the idea. ^_^ –  chad Apr 13 '13 at 2:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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