Deadlock when we we have dependent entities in hibernate and try to update table using multiple threads in Vertica

I have two java classes. Father.java and Children.java

    @Entity
    @Table(name = "FATHER")
    @JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
    class Father implements Cloneable
    {
    @Id
    @Column(name = "father_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long fatherId;
    
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "father_id")
    @Fetch(value = FetchMode.SUBSELECT)
    private List<Children> children = new ArrayList<Children>();
    //getter and setters and public constructors 
    }


    @Entity
    @Table(name = "Children")
    class Children implements Comparable<Children>
    {

    @JsonIgnore
    @Id
    @Column(name = "children_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long child_id;

    @JsonIgnore
    @Column(name = "father_id")
    private long fatherId;
    //public constructors and getters and setters
    }

    public interface RelationDao{
    
    public Father update() throws Exception;
    
    }
    @Repository("relationDao")
    @EnableTransactionManagement
    @Transactional
    public RelationDaoImpl{
    @Override
    @Transactional("txManager")
    public Father update(Father father)
    {
    father = merge(father);
    //added retry logic as well also father is updated with a new child which is why merge
    }
    }

​x
 
    @Entity    @Table(name = "FATHER")    @JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })    class Father implements Cloneable    {    @Id    @Column(name = "father_id")    @GeneratedValue(strategy = GenerationType.IDENTITY)    private Long fatherId;        @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)    @JoinColumn(name = "father_id")    @Fetch(value = FetchMode.SUBSELECT)    private List<Children> children = new ArrayList<Children>();    //getter and setters and public constructors     }​​    @Entity    @Table(name = "Children")    class Children implements Comparable<Children>    {​    @JsonIgnore    @Id    @Column(name = "children_id")    @GeneratedValue(strategy = GenerationType.IDENTITY)    private long child_id;​    @JsonIgnore    @Column(name = "father_id")    private long fatherId;    //public constructors and getters and setters    }​    public interface RelationDao{        public Father update() throws Exception;        }    @Repository("relationDao")    @EnableTransactionManagement    @Transactional    public RelationDaoImpl{    @Override    @Transactional("txManager")    public Father update(Father father)    {    father = merge(father);    //added retry logic as well also father is updated with a new child which is why merge    }    }​​

I receive the Deadlock X exception if several threads visit the same table (entity father) to updates with distinct row entries, even though the records are different.

Is there any way to fix why the entire table locks up instead than just one row? Even though I haven’t updated or added anything to the code, the transaction isolation level is SERIALIZABLE.

DB system is Vertica

Answer

Explained here, if anyone is coming here to check why Vertica doesn’t support row level locks for updates or delete. https://stackoverflow.com/a/69917383/8799862

So I used synchronized to perform thread-safe updates and deletes.

Advertisement

Answer