When many threads attempt to update different records in the same table, I receive a deadlock X. Is there a method to get row level locks rather than table level locks when using the Vertica DB system with Java JPA?

CodePudding user response：

No.

Vertica is a dedicated Big Data database, designed over 15 years ago with OLAP workloads in mind - at the detriment of a support for OLTP workloads.

Row level locks are a thing of OLTP databases.

Vertica can commit and roll back transactions of several million rows without any stress at all. To be able to do that, it had to penalise the support for single row transactions.

A part of the design supporting this behaviour is that it is an insert-only database. Deletes are markings of deletion that get resolved later in the background; the row remains, marked as deleted. During a de-fragmentation run in the background, later, deleted rows get physically removed.

Updates are deletes followed by inserts.

Every Delete transactions obtains and keeps an exclusive 'X' lock on the affected table, for the duration of the transaction.