5

I'm new to database and I'm lost with transaction. For example in a situation where I have a table sensor_log that receives INSERT constantly and I want to move data to two other tables in one transaction.

BEGIN;

INSERT INTO sensor_log_a
SELECT id, location 
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

INSERT INTO sensor_log_b
SELECT id, location 
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

COMMIT;

Is there a risk that data is different between sensor_log_a and sensor_log_b if data gets inserted during the transaction or the BEGIN;..COMMIT; prevent this?

  • What does USING (location) do? – paparazzo Jan 6 '17 at 18:02
  • @Paparazzi The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s) – Mio Jan 7 '17 at 14:50
10

A statement sees a consistent view of the "world" based on the data that was there when the statement started. So the select statements in your two insert statements will not see new rows while they are running.

However, if the table is changed between running the two statement (or after the first one started), each select statement could see different data e.g the first one sees 100 rows, the second one 200.

If you want to ensure that the whole transaction sees a consistent view of the data, use a higher isolation level. In your case using repeatable read would be enough.

begin transaction isolation level repeatable read;

...
commit;

Another option would be to do this in a single statement using a data modifying cte:

with to_insert as (
  SELECT id, location 
  FROM sensor_log
  INNER JOIN sensor_location_to_insert USING (location)
), insert_a as (
  insert into sensor_log_a
  select *
  from to_insert
)
insert into sensor_log_b
select *
from to_insert;
  • 1
    "However the view that each select statement has, might be different (e.g the first one sees 100 rows, the second one 200)" if the select are different? I think I don't see properly differences between your two first paragraphs. – Mio Jan 6 '17 at 13:28
  • 1
    @BeniMio: what I mean is that between running the first and the second statement there might be changes to the underlying table. So the first statement might see different rows then the second. I changed my wording. Is that clearer now? – a_horse_with_no_name Jan 6 '17 at 13:29
  • @a_hose_with_no_name But it's in contradiction with "So the select statements in your two insert statements will not see new rows."? Thank you for your patience. – Mio Jan 6 '17 at 13:34
  • 2
    @BeniMio: a single statement will not see any changes to the underlying tables while it's running. But the next statement will see those changes (with "read committed") – a_horse_with_no_name Jan 6 '17 at 13:41
  • 2
    REPEATABLE READ is definitely the right choice here. – Craig Ringer Jan 6 '17 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.