Search code examples
mysqlinnodbmyisamrace-conditionon-duplicate-key

Concept of Race Conditions

I know what a race condition is but I still don't fully understand:

Firstly, I know myisam does not support transactions and innodb does.

I am building a social site with a lot of users in mind and I wonder if I need to think about race conditions, because users will update their profile with ON DUPLICATE KEY UPDATE, but I wonder if a race condition will occur and how do you write code to protect against this issue?

Can someone please explain the concept of race condition on a site with multiple users e.g. use of COMMIT, ROLLBACK etc.

Where do I use these things?

Solution

  • Firstly, I know myisam does not support transactions and innodb does.

    That's a good start. I suggest using InnoDB. Transactions are what make the modifications of a database atomic and consistent. Transactions also ensure isolation, so that no user of the database can interfere with the modifications of another user.

    Race conditions can occur when there is multithreading. Yes, a database uses more than one thread, but transactions assure that one user's database changes are made before another user's database changes. (Yes, that's a simplified logical explanation.)

    Edited to add: You can ask specific SQL question you might have in other Stack Overflow quesitons, but in general, you want to COMMIT as soon as possible after an INSERT, UPDATE, or DELETE SQL statement.