TOPIC: MySQL Transaction / Table Locks

Hi there,

I want to request some more details about how the MySQL writeAdapter handles insert records.

Does it create a transaction around the entire batch when executed?
Does it lock the table for updates?

I am asking because I am having some problems when running large batch imports (50 000 to 100 000 records) against a table, other processes reading/updating this table seem to hit a transaction time out on a lock?

would you suggest to rather go the route of generating SQL script and running that script afterwards?

PS: Still loving the product!!
PPS: I hear XML is on the way - is JSON support in the pipeline?

Hello Heinz,

Usually FlowHeater creates one big transaction during the entire import/update process. This means the affected records are blocked for updates for other application/connections. Note: The certain behavior depends on the used database and the used transaction level as well.

Of course you could change the FlowHeater standard transaction behavior on the “Advanced” tab in the MySQL Adapter configurator.
Here you can disable the “Use Database Transactions” option in general for the import/update process, not recommended. This will degrees the database performance a lot.

A more better and recommended way for large batch import processes is to enable the “Perform Auto Commit after x written records”. In your case I would suggest 1000 (or 5000) records. This option should block other applications/connection just a few seconds (1 or 2) and the database import performance should be quite the same like before.



Yes, an XML Adapter is coming soon Below you find two links were you may download an early Alpha Version of V4. This Version can be used without any restriction as long V4 isn’t official released. Feel free to test it!

JSON support is planned for next year.

FlowHeater V4 Alpha 32 bit
FlowHeater V4 Alpha 64 Bit

Hi,

Found this thread while searching for JSON. I am wondering whether JSON is or will be supported.

Thanks in advance,

Best regards, Alexander

Hi Alexander,

I´m afraid until now JSON is just planned but because less inquiries currently not implemented yet.