Postgresql – avoid lock and allow two scripts to insert data in one table

lockingpostgresql

I have a database called db1 and I'm filling it with other two different databases, let's call db2 and db3. I connect db2 and db3 to db1 using postgres_fdw and now I'm running scripst like this:

insert into db1.table from select a,b,c from db2.table

and

insert into db1.table from select a,b,c from db3.table

turns out that only the first script is running, the second is waiting with idle in transaction. This is what htop shows me from the second script, while the first is ok.

looking for reasons, I found out that this may be happening due to a lock, so investigating on postgres, I also found this

I'm inserting data into interactions and each interactions has user, so I'm selecting the correct user before insert into interaction.
Is there a way to bypass the lock? If so, is it safe?

Best Answer

idle in transaction means it is waiting for data from the client, not that is waiting on a lock.

And your excerpt from pg_lock supports this, assuming the last column is the "granted" column. All locks have been granted, no one is blocking.

If your scripts are blocking each other, it seems to be happening outside of the database. Or at least, outside of that particular database.

Simplified scenario:

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost`, `idrevision`),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `user1_idx` (`iduser`));

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, content`) VALUES (1, 1, 1, 'this is old content of first post');

Session1:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES  
  ((SELECT max_idrevision + 1 
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1), 
   1, 2, 'abc xyz');

select max(idrevision) AS max_idrevision from revision where idpost=1;

The above query will fetch the value from the secondary index 'index1'. Because idpost and idrevision is covered by index1 itself. So index1 is a covering index for this query.
It will take S lock on the row(1,1) in the index 'index1' due to repeatable read isolation level. (Read about repeatable read isolation level).

Session2:

BEGIN;
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;

Above query will change the clustered index alone, it won't change any secondary index like 'index1' because 'content' is not part of any secondary index.

UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;

But in this case, idrevision has been changed. So it will try to update the key part of 'index1' ( because idrevision is a part of key in index1).

Why it leads to hang:

As I mentioned earlier in session 1, S row lock taken for the row (1,1) in the index1.
Now session 2 tries to take X row lock to update the row(1,1) in the same index1.
If you look lock type compatibility matrix, S and X locks are Conflicts.

Note:

During hanging, you can open one more session and give the following query to find out where it is hanging.

mysql> select * from information_schema.innodb_locks;
--------------
select * from information_schema.innodb_locks
--------------

+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| lock_id    | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| 1826:8:4:2 | 1826        | X         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
| 1817:8:4:2 | 1817        | S         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+

The above analysis is based on mysql-5.6

SQL Server Table Locking – How to Prevent Locking Entire Table on Insert or Update?

Chances are it isn't locking the "whole table".

It is locking a row in the table but your SELECT * FROM LAYOUTSv2 tries to read the whole table so necessarily is blocked by that lock.

For the insert case you can merely specify the READPAST hint to skip past the locked row- however that will not give your desired result for the UPDATE case (it will skip the row again not read the starting version of the row).

If you configure the database for read committed snapshot isolation this will give your desired effect for both cases (at the expense of greater use of tempdb)