Transaction and Locking with multiple threads

Question

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 ?

I guess the reader gets no data because he tries to set a read lock (depends on isolation level) and doesn't get the lock because of your transaction. So he could try to reda with nolock. — user743414, Sep 19 '16 at 07:38

score 1 · Answer 1 · edited May 23 '17 at 12:00

1

You're seeing an empty table since truncate table has an implicit commit. If you need to change the entire table in a transaction you can use delete then insert, or try the rename solution presented in this answer

edited May 23 '17 at 12:00

Community

1
1

answered Sep 19 '16 at 09:45

C. Broadbent

753
4
11

Thanks a lot Broadbent for this information. I am going to implement this solution now. – birubisht Sep 19 '16 at 09:59

score 1 · Accepted Answer · answered Sep 23 '16 at 00:32

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.

Transaction and Locking with multiple threads

2 Answers2