#Mysql transaction how to
#Mysql transaction update

It would then need to check if there's a pending value when performing a select. Perhaps it temporarily holds the value of the field somewhere waiting for the transaction to finish? I've also experimented with databases and tables that are GB's in size and take minutes to dump, there's no way it's making entire copies. You may not touch a table until 10 minutes into the transaction and yet the data is at it was 10 minutes ago, no matter how many modifications other processes made in the mean time. I don't think it takes a copy of the database or table, since when you begin a transaction it doesn't know what tables you're going to touch.

MySQL must then be storing the value of this field twice in some way. So now I could execute SELECT name FROM names WHERE id = 31 at the exact same time and have one return 'John' and one return 'Jane' depending on whether I was in a transaction, or when the transaction started. 12:00:31 - Row 31 is selected from the first transaction and still returns 'John' rather than 'Jane'.12:00:30 - All rows are updated to Jane.12:00:00 - All rows say John and a select begins in a transaction that takes 1 second per row.

#Mysql transaction update

In my first process, within the transaction, I will still receive 'John' as a result even after the update to 'Jane' has completed. Each select query takes 1 second so this takes 100 seconds.Īfter 50 seconds another process, not within a transaction, updates every row to 'Jane'. I have a table of 100 people named 'John' and I start a transaction in which I loop through and select each row one by one. This is all fine and i'm not really confused about this works.

If I make a select after the transaction is committed it will return 'Jane'. If I make a select from another process during that 100 seconds the result will be 'John' rather than 'Jane'. I have a table of 100 people named 'John' and I update every name to 'Jane' in a loop inside a transaction, each update takes 1 second meaning it takes 100 seconds to finish the transaction. Selecting before another transaction has committed What I am not sure about is how MySQL manages to hold all the data over long periods of time and what affect this might have performance.

#Mysql transaction how to

I understand that a MySQL transaction will let you perform multiple inserts/updates at once where either all or none of them will succeed and I understand how to use them.