Devart Forums

I have a number of tables in my application, and I'm trying to get a better understanding of whether to use InnoDB or MyISAM structures for them. I've read a great deal of material concerning row vs. table level locking, and have a pretty good understanding of these topics and how they relate to each structure type.

My question which I thought I'd post concerns how the MySQLDataTable (and MySQLConnection) objects handle cases of table level locking concurrency issues.

By way of example, suppose I have two processes which happen to be editing data belonging to the same table (but different rows within that table). The table in question has about 65,000 rows, and the two processes are constantly updating data, sending UPDATE commands through the MySQLDataTable object in each process, at a peak rate of about once each 30-40 milliseconds. The two processes would never be updating the same records (they are load balanced processes for just this reason), but they could very well be sending an update command at approximately the same time.

In the event that process #2 attempts an update while process #1 is already performing an update, would the table level lock be handled within the MySQL database engine and the update from process #2 be queued, or would a concurrency violation be thrown immediately for process #2 in the MySQLDataTable object?

Currently my tables are InnoDB structure, so I don't run into this problem since my two processes never attempt to update the same records. If I were to switch, should I expect to see (and handle) concurrency level violations at the table level?

Thanks,

John

ps - These are straight MySqlDataTable.Update commands on single tables for each call. No Transactions to worry about. No referential integrity to worry about.

When you post changes to database, and the table is locked by another connection, MySQL Server waits until lock is released, then it executes a command. During this process MySqlDataTable.Update() does not return. The wait timeout (for innodb tables) is controlled by innodb_lock_wait_timeout server's configuration parameter. If the lock is not released during this time MySQL Server breaks command execution, and exception is thrown by MySQLDirect .NET. If other client modifies same row, and command does not update the table (depending on conflict detection you used) a concurrency violation occurs.

Thank you very much for the response. That's exactly what I needed to know.

And my apologies if that was in the documentation and I didn't dig enough to find it.

John

Note that if MySqlCommand.CommandTimeout is smaller than innodb_lock_wait_timeout, you will not receive timeout error from MySQL (server error) saying that table lock was not released, you'll get an exception from MySqlCommand (client error) saying that command execution has timed out. In the first case connection will remain open, in the second case it will be closed.