I'm trying to understand what goes on behind the scenes in a typical MySQL database.

Say you have a table called Employees, and it has n employees (n being a size large enough where any significant changes such as adding n employees would take a long time to make)

Then say you want to delete all n and then add a new set of n new employees, so in your program you have a Delete and Insert being committed together.

Now say that someone calls a Select statement on your database while your commit is taking place.

What does the user see?

A ) Assuming this doesn't already happen, how do you ensure that the user can see the old employees until all the new employees are done being committed.

B ) Alternately, how do you ensure that the user sees the table in real-time. In other words, if you deleted all the employees and then started adding new ones, they can see the only the ones you have added so far.

This post was edited by oOn on Apr 26 2015 08:40pm

i dont have much experience with mysql specifically (moreso with sqlite, postgres, oracle, and mssqlserver), but they should see what was in the database prior to your transaction if you haven't committed yet. should be easy for you to test this.

create a connection / statement to delete/insert.
create a separate connection/statement to select.
delete/insert but don't commit yet.
select from the table
commit
select again

A and B are two separate behaviors, and your commit order depends on which behavior you want.

for option A: delete n employees, insert m employees, commit at the end
for option B: delete n employees, insert m employees, committing after each new employee was inserted

or perhaps what you want is first insert m employees, committing after each new insert, then delete the original n afterwards? then they'll see all the new employees as they're being added + original employees, then after all the new employees are done they won't see the original employees anymore

This post was edited by carteblanche on Apr 26 2015 08:52pm

In option a : delete n employees, insert m employees, commit at the end

How does a commit work exactly and how quickly does it work? What if a user tries to read during this commit?

did you skip the first half of my post?


select again = prior to commit completing

let me rephrase, what does a program do behind the scenes when you make a commit?

This post was edited by oOn on Apr 26 2015 09:12pm

seems like an odd question since i dont see the relevance. on a user (eg: app developer) side, all that should matter is the contract mysql provides. i assume you already know at a high level what it's doing. the internal workings shouldn't matter. i could probably go on about B+ trees for RDBMS in general, but i don't know mysql specifically.

but since you're interested, you're in luck: http://dev.mysql.com/doc/internals/en/index.html

their transaction lifecycle link should be a bit helpful. if you want to get more detailed, you can always pull down the code and look at it yourself.

Hey carteblanche, thanks for your help. After actually testing the code you wrote I got a stronger sense of how it works.

Seems like in order for a commit to work, it has to keep the uncommitted data and the original data both stored in memory before clearing the original data, which I guess is pretty obvious. but i wanted to make sure because if you work with a ton of data, it can become a problem to house both sets of data before the commit completes...

does it matter where it's stored? for all you care, they could clone the table when you start the transaction, modify the clone'd copy while everyone else is still using the original table, then after the commit is done start pointing new queries to the clone'd table and drop the original. where it's stored isn't relevant. what matters is that external connections only see data that is committed, not data prior to commit.

I guess it's rare, but sometimes it does matter, because you could be dealing with tables that consist of gigs of data and if your commit has to modify multiple tables, you would be building up terabytes of data

This post was edited by oOn on Apr 29 2015 06:39am

What RDBMS does this? Oracle's buffer cache, for instance, will flush to disk after hitting a certain memory threshold. All it's storing are change vectors and those don't necessarily have to be in memory all the time.