Search code examples
c++mysqldatabaseinnodb

Transaction and Locking with multiple threads

Hi All following is the problem scenario:

I am using MYSQL (Innodb engine), one of my application(C++/MYSQLCAPI) is doing following operation :

START TRANSACTION

truncate my_table

load Data infile into table my_table.

if both the above command [truncate and load ] are successful then COMMIT

else ROLLBACK

now another application(C++/MYSQLCAPI) which is reading this table in every second by following command.

select * from my_table

ERROR: in this read attempt sometime it gets 0 data , what could be the reason for this ?

Solution 
  • CREATE TABLE new LIKE real;
load `new` by whatever means
if something went wrong, don't do the next two steps.
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

    This avoids the problem you mentioned, plus lots of other problems. In particular, it needs no special transaction handling; the RENAME is atomic and very fast.