Geek Answers Handbook

How to atomically move rows from one table to another?

I collect data from several thousand sensors and store them in a MySQL database. There are several hundred attachments per second. To improve insert performance, I first store the values ​​in the MEMORY buffer table. After a minute, I run a stored procedure that moves the inserted rows from the memory buffer into a permanent table.

Basically, I would like to do the following in my stored procedure to move lines from a temporary buffer:

INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;

Unfortunately, the previous one is not used, because the data collection processes insert additional rows in the "data_buffer" between INSERT and DELETE above. Thus, these rows will be deleted without adding data to the table.

How can I make an atom atom or force a DELETE statement to delete only rows that were SELECTED and INSERTed in the previous statement?

I would prefer to do this in a standard way, which, if possible, works on different database machines.

I would prefer not to add extra id columns due to performance and storage issues.

I would like for standard SQL or something similar to have a SELECT_AND_DELETE or MOVE statement ...

+5
source share
6 answers

, , - data_buffer ( data_buffer1 data_buffer2); data_buffer2, insert delete on data_buffer2; , data_buffer2, + data_buffer1 data.

+1

, , ,

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO data (SELECT * FROM data_buffer FOR UPDATE); 
DELETE FROM data_buffer; 
COMMIT TRANSACTION;
+3

, , <= max (id)

+1

@ammoQ. , , INSERTing , , .

RENAME :

CREATE TABLE IF NOT EXISTS data_buffer_new LIKE data_buffer;
RENAME TABLE data_buffer TO data_buffer_old, data_buffer_new TO data_buffer;
INSERT INTO data SELECT * FROM data_buffer_old;
DROP TABLE data_buffer_old;

, RENAME , INSERTing , " ". MySQL.

+1

, , ? , where... - :

DELETE FROM data_buffer 
WHERE primarykey IN (SELECT primarykey FROM data)
0

MySQL. , INSERTing .

, , :

LOCK TABLE data_buffer READ;
INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;
UNLOCK TABLE;

, INSERT , :

LOCK TABLE data_buffer WRITE;
INSERT INTO data_buffer VALUES (1, 2, 3);
UNLOCK TABLE;

The INSERT process will obviously be blocked while the lock is in place.

0
source

More articles:


All Articles