Transaction with many INSERT'S without locking the table

2
votes

I've got script that is creating same data in database. It looks like:

START TRANSACTION;
INSERT INTO table (data);
INSERT INTO table (data);
INSERT INTO table (data);
...
COMMIT;

The script run for about 30 minutes. But when it's working, the table is locked for other INSERTS. I'm not able to INSERT any row from in another process until the script ends. Is it necessary? I'd like to use transaction, but I can't lock whole table. Is there any way to do that?

2
mysqltransactionslocking

2 Answers

3
votes

Can you instead do:

START TRANSACTION;
INSERT INTO table (data);
COMMIT;
START TRANSACTION;
INSERT INTO table (data);
COMMIT;
...
START TRANSACTION;
INSERT INTO table (data);
COMMIT;

Not the most elegant, but it should solve your problem based upon the situation you describe.

If you need the whole item to be one TRANSACTION, then the best way to do things would be as follows:

INSERT INTO tmpTableA (dataX);
INSERT INTO tmpTableB (dataY);
INSERT INTO tmpTableC (dataZ);
...
'   Do all the "building / processing above using temp tables either in memory or on disc, and then only when all the "heavy lifting" is done; update/insert the data into your tables.
...
START TRANSACTION;
INSERT INTO tableA (tmpTableA);
UPDATE tableB.foo = tmpTableB.fee WHERE BlahBlobBlah;
INSERT INTO tableC (tmpTableC);
COMMIT;

You want to hold off on starting the TRANSACTION, until all of the processing is done.

0
votes

You should create a temp table: table_temp insert all data to table_temp, when you ready you can insert all temporary data into final table:

START TRANSACTION;
INSERT INTO table_temp (data);
INSERT INTO table_temp (data);
INSERT INTO table_temp (data);

insert into table
select * from table_temp;

commit;