6

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?

2
  • What does USING (location) do?
    – paparazzo
    Commented Jan 6, 2017 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
    Commented Jan 7, 2017 at 14:50

1 Answer 1

12

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;
6
  • 2
    "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
    Commented Jan 6, 2017 at 13:28
  • 2
    @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?
    – user1822
    Commented Jan 6, 2017 at 13:29
  • 1
    @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
    Commented Jan 6, 2017 at 13:34
  • 3
    @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")
    – user1822
    Commented Jan 6, 2017 at 13:41
  • 3
    REPEATABLE READ is definitely the right choice here. Commented Jan 6, 2017 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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