CODEWITHSUNDEEP
mysqlmultithreadinginnodbmyisammysql-connector
Atul
Atul

Reputation: 4320

MySQL: Concurrent updates (through threads) on a simple table

In my application (VC++/Windows 8) I am having 7 threads each have opened connection to MySQL database. All these threads concurrently try to increment value of single field in a table.

To do this I've created a sample table DEMO_TABLE having columns MyIndex and MyCounter (both integers) and added a row to it having MyIndex field value 0. Then I am calling executeUpdate through each thread using MySQL Connector C++ :

executeUpdate("UPDATE DEMO_TABLE SET MyCounter = (MyCounter + 1) WHERE MyIndex = 0");

Here I am not using any locking (row or table lock) still the code didn't give me any error or exception. When I checked the value of MyCounter I saw it got increased. So this seems working correct.

But has raised me these questions:

  1. By default MySQL uses MyISAM engine which needs to lock table for concurrent update query execution. But I am not locking table here, how does this code work without throwing any exception?

  2. Does executeUpdate implicitly uses any lock?

(As per my knowledge InnoDB provides row level locking mechanism which I plan to use in my code. But before that I just wanted to try on my own what happens with default engine without any lock. I was expecting I would get some exception which would tell me about race condition so that I can verify the same doesn't happen with the use of lock)

Upvotes: 3

Views: 4095

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179084

The locking is implicit, yes, but it's not being done by executeUpdate(). The storage engine in MySQL handles the locking and the unlocking.

Any time you write to a MyISAM table, your query waits the write lock on the table to be available, the write lock is acquired, the write is done, and the write lock is released. There is no genuine write concurrency in MyISAM because each worker is in fact waiting in line for the write lock. You don't get an error because the write requests are serialized.

The situation with InnoDB is similar but very different, in that InnoDB only locks a portion of the table, typically at the row level, where InnoDB can lock a range within an index, thereby locking the rows at that range in the index (and the gap that precedes them). This locking is more granular than table locking, allowing improved concurrency behavior, but there is no concurrent operation on the same row -- each worker waits for the lock or locks that it needs.

In both cases, the locks are implicitly taken.

Upvotes: 3

Related Questions