What happens if data gets inserted in the middle of a transaction?

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?

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;

StackExchange Database Administrators Q#160167, answer score: 12