[jira] [Commented] (OPENJPA-2905) Sequences are altered unnecessarily

["Pawel Veselov (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-2905?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17698681#comment-17698681 ] 

Pawel Veselov commented on OPENJPA-2905:
----------------------------------------

I'm actually not sure why the sequence altering DDL was blocked, I don't think it's because of the lock, most likely because of of another sequence having been modified by pid 29611. In which case, OPENJPA-2614 change may be sufficient. But it's still not cool doing DDL during operations.

For kicks, this is how I fixed this for our clone : https://github.com/veselov/openjpa/commit/ad072af21e1b5e3d5278c0c7ff463f4238f1050b

> Sequences are altered unnecessarily
> -----------------------------------
>
>                 Key: OPENJPA-2905
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2905
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 3.1.0, 3.1.1, 3.1.2, 3.2.0, 3.2.1, 3.2.2
>            Reporter: Pawel Veselov
>            Priority: Major
>
> {{NativeJDBCSeq.allocateInternal()}} attempts to alter a sequence for every sequence encountered at least once during a runtime.
> In Postgres, for example, executing this DDL requires that no other locks are held, we are seeing this blockage:
> {noformat}
> blocked_pid                           | 12519
> blocked_user                          | snapstore
> blocking_pid                          | 29611
> blocking_user                         | snapstore
> blocked_statement                     | ALTER SEQUENCE campaign_seq INCREMENT BY 50
> current_statement_in_blocking_process | select id from other_lock where id = $1 for share 
> {noformat}
> It doesn't make sense to alter the sequence if the sequence already has the right increment, otherwise there are the following issues:
> 1. In a cluster, the alterations are going to be attempted by each node
> 2. If there is a sudden request, in the middle of operations, to get a sequence value for some rarely written to table, that sequence can be held up for a long time, unwarranted so, at least in Progress
> I really recommend that the sequence increment is first checked, and is only changed if it doesn't match the expectation, at least on the databases where this is possible.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)