MySQL query synchronization/locking question

Question

I have a quick question that I can't seem to find online, not sure I'm using the right wording or not.

Do MySql database automatically synchronize queries or coming in at around the same time? For example, if I send a query to insert something to a database at the same time another connection sends a query to select something from a database, does MySQL automatically lock the database while the insert is happening, and then unlock when it's done allowing the select query to access it? Thanks

Possible duplicate of [What's the difference between MyISAM and InnoDB?](https://stackoverflow.com/questions/12614541/whats-the-difference-between-myisam-and-innodb) — Madhur Bhaiya, Nov 02 '18 at 16:36
"if I send a query to insert something to a database at the same time another connection sends a query to select something from a database, does MySQL automatically lock the database while the insert is happening, and then unlock when it's done allowing the select query to access it? " Best answer MySQL never locks a database, a table can be locked depending on the used table engine like @MadhurBhaiya commented. — Raymond Nijland, Nov 02 '18 at 16:42
@RaymondNijland I guess my next question would be if a user locks a table with a LOCK query, then another connection tries to access that table, will the other connection's query simply wait until the table is unlocked? Or will the query just return an error — Jeb, Nov 02 '18 at 16:45
"if a user locks a table with a LOCK query, then another connection tries to access that table, will the other connection's query simply wait until the table is unlocked? Or will the query just return an error" bit tricky to answer @Jeb because it's allowed by MySQL to get multiple READ locks on the same table from different connections/sessions. — Raymond Nijland, Nov 02 '18 at 16:55

score 1 · Accepted Answer · answered Nov 02 '18 at 17:26

Do MySql databases automatically synchronize queries coming in at around the same time?

Yes.

Think of it this way: there's no such thing as simultaneous queries. MySQL always carries out one of them first, then the second one. (This isn't exactly true; the server is far more complex than that. But it robustly provides the illusion of sequential queries to us users.)

If, from one connection you issue a single INSERT query or a single UPDATE query, and from another connection you issue a SELECT, your SELECT will get consistent results. Those results will reflect the state of data either before or after the change, depending on which query went first.

You can even do stuff like this (read-modify-write operations) and maintain consistency.

 UPDATE table 
    SET update_count = update_count + 1, 
        update_time = NOW()
  WHERE id = something

If you must do several INSERT or UPDATE operations as if they were one, you'll need to use the InnoDB engine, and you'll need to use transactions. The transaction will block SELECT operations while it is in progress. Teaching you to use transactions is beyond the scope of a Stack Overflow answer.

tadman · Answer 2 · 2018-11-02T17:25:56.247

The key to understanding how a modern database engine like InnoDB works is Multi-Version Concurrency Control or MVCC. This is how simultaneous operations can run in parallel and then get reconciled into a consistent "view" of the database when fully committed.

If you've ever used Git you know how you can have several updates to the same base happening in parallel but so long as they can all cleanly merge together there's no conflict. The database works like that as well, where you can begin a transaction, apply a bunch of operations, and commit it. Should those apply without conflict the commit is successful. If there's trouble the transaction is rolled back as if it never happened.

This ability to juggle multiple operations simultaneously is what makes a transaction-capable database engine really powerful. It's an important component necessary to meet the ACID standard.

MyISAM, the original engine from MySQL 3.0, doesn't have any of these features and locks the whole database on any INSERT operation to avoid conflict. It works like you thought it did.

When creating a database in MySQL you have your choice of engine, but using InnoDB should be your default. There's really no reason at all to use MyISAM as any of the interesting features of that engine (e.g. full-text indexes) have been ported over to InnoDB.

MySQL query synchronization/locking question

2 Answers2