Home » Eclipse Projects » EclipseLink » sequence getting out of sync
|
Re: sequence getting out of sync [message #505756 is a reply to message #505714] |
Mon, 04 January 2010 19:04 |
|
I cannot see how this is possible. EclipseLink always writes to the database before using a sequence value.
Are you changing your preallocation size dynamically? How is your sequence and connection pooling configured, and how are you allocating sequence numbers?
James : Wiki : Book : Blog : Twitter
|
|
|
Re: sequence getting out of sync [message #505762 is a reply to message #505756] |
Mon, 04 January 2010 19:51 |
Tom Eugelink Messages: 817 Registered: July 2009 |
Senior Member |
|
|
On 2010-01-04 20:04, James wrote:
> I cannot see how this is possible. EclipseLink always writes to the
> database before using a sequence value.
>
> Are you changing your preallocation size dynamically? How is your
> sequence and connection pooling configured, and how are you allocating
> sequence numbers?
No. The strangest thing is that this popped up quite unexpectedly. I had a stable system using the 1.1 branch for a long time and switched to 2.0.0RC about two weeks before Christmas (skipping 1.2.0). And just before Christmas suddenly all kind of sequences were starting to 'lag'. I didn't have time to investigate anymore, so I downgraded back to 1.1.3 and have a "panic.sql" running every 15 minutes to reset the most used sequences to their max(PK) value. I was going on vacation during Christmas, and nobody called, so it worked. And it was good excuse to bring the laptop on vacation ;-)
Now I have to start digging into what is causing this and could use some pointers where to look.
Fact is that the sequences lagged under 2.0.0RC1 - *that is 100% sure*; I kept resyncing the sequences many times a day (while I was downgrading and testing that build).
Possibly also under 1.1.3, at least the one test we did today had this behavior, but the panic.sql was still running and that it may have conflicted. Going to rerun that test Wednesday.
The big changes that I made recently, is switching from using getSingleResult to find because of the caching, and from JPA events to Eclipselink events.
I also should have a look if I made some changes in the way the pool is setup. I remember fiddling with the parameter map for the EMF when upgrading to 2.0.0RC1. I think I had "commit using the same connection" in there, because of some locking that I was trying to do.
Any suggestions where to look are highly appreciated.
Tom
|
|
| | |
Re: sequence getting out of sync [message #506186 is a reply to message #506179] |
Wed, 06 January 2010 13:40 |
Tom Eugelink Messages: 817 Registered: July 2009 |
Senior Member |
|
|
> I am not sure JOIN_EXISTING_TRANSACTION also means that the sequence SQL
> should go through the same connection.
"Real" sequences are not part of any transaction; once a sequence has generated a value, that value can never be generated again, rollback or not.
So I think sequence SQLs should always use their own connection and commit after each increment.
Illustration of the problem:
License lLicense = new License();
try
{
lEntityManager.getTransaction().begin();
lEntityManager.persist(lLicense);
lEntityManager.getTransaction().commit();
}
catch (Throwable t)
{
System.out.println(t.getMessage());
}
lLicense.setRoyaltyPercentage(BigDecimal.ZERO);
try
{
lEntityManager.getTransaction().begin();
lEntityManager.persist(lLicense);
lEntityManager.getTransaction().commit();
}
catch (Throwable t)
{
System.out.println(t.getMessage());
}
Before in the database (select 'max PK', max(licensenr) from license union all select 'sequence', seq_count from sequence where seq_name = 'licensenr'):
max PK 355
sequence 355
Output (the value in square brackets is the connection):
....
[C32623606]: [1] setInt=1 / java.lang.Integer / UPDATE sequence SET seq_count = seq_count + >>>HERE<<< WHERE seq_name = ?
[C32623606]: [2] setString=licensenr / java.lang.String / UPDATE sequence SET seq_count = seq_count + ? WHERE seq_name = >>>HERE<<<
[C32623606]: executeUpdate: UPDATE sequence SET seq_count = seq_count + ? WHERE seq_name = ?
[C32623606]: [1] setString=licensenr / java.lang.String / SELECT seq_count FROM sequence WHERE seq_name = >>>HERE<<<
[C32623606]: executeQuery: SELECT seq_count FROM sequence WHERE seq_name = ?
....
[C32623606]: [1] setBigDecimal=356 / java.math.BigDecimal / INSERT IGNORE INTO license (licensenr, startdate, copyright, royalty_percentage, required_samples, payment_start_date, payment_start_amount, guarantee, sell_off, report, lazylock, advance, remark, external_number, geo_limit, title, dwhmodified, dwhby, enddate, sales_contactnr, royalties_contactnr, artwork_contactnr, reinders_contactnr, currencynr, reinders_relationnr, relationnr, license_paymentenginenr) VALUES ( >>>HERE<<< , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[C32623606]: [4] setNull=3 / java.lang.Integer / INSERT IGNORE INTO license (licensenr, startdate, copyright, royalty_percentage, required_samples, payment_start_date, payment_start_amount, guarantee, sell_off, report, lazylock, advance, remark, external_number, geo_limit, title, dwhmodified, dwhby, enddate, sales_contactnr, royalties_contactnr, artwork_contactnr, reinders_contactnr, currencynr, reinders_relationnr, relationnr, license_paymentenginenr) VALUES (?, ?, ?, >>>HERE<<< , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
....
[C32623606]: commit: java.lang.reflect.InvocationTargetException->java.sql.SQLException: Cannot insert a null into column (license.royalty_percentage).
[C32623606]: rollback()
....
!!! no sequence SQL (because the entity already has a @Id value)
....
[C32623609]: [1] setBigDecimal=356 / java.math.BigDecimal / INSERT IGNORE INTO license (licensenr, startdate, copyright, royalty_percentage, required_samples, payment_start_date, payment_start_amount, guarantee, sell_off, report, lazylock, advance, remark, external_number, geo_limit, title, dwhmodified, dwhby, enddate, sales_contactnr, royalties_contactnr, artwork_contactnr, reinders_contactnr, currencynr, reinders_relationnr, relationnr, license_paymentenginenr) VALUES ( >>>HERE<<< , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[C27032609]: [4] setBigDecimal=0 / java.math.BigDecimal / INSERT IGNORE INTO license (licensenr, startdate, copyright, royalty_percentage, required_samples, payment_start_date, payment_start_amount, guarantee, sell_off, report, lazylock, advance, remark, external_number, geo_limit, title, dwhmodified, dwhby, enddate, sales_contactnr, royalties_contactnr, artwork_contactnr, reinders_contactnr, currencynr, reinders_relationnr, relationnr, license_paymentenginenr) VALUES (?, ?, ?, >>>HERE<<< , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
....
Afterwards in the database:
max PK 356
sequence 355
The next run will throw an exception.
|
|
|
Re: sequence getting out of sync [message #506961 is a reply to message #505714] |
Mon, 11 January 2010 14:46 |
|
I suppose that in general it is best to not continue to used objects from a failed transaction. They can have lingering data from the transaction, such as your id, or version numbers, or references to other objects in the failed transaction. If a transaction fails, best to restart from scratch.
If you want to reused the failed objects, then you should reset the Id to null or 0, or keep a copy of their original state before the persist.
For sequencing you can use a sequence connection pool, which is definitely recommended if you are using TABLE sequencing.
"eclipselink.jdbc.sequence-connection-pool"="true"
For connections, another way to use the same connection for read and write is exclusive connections,
"eclipselink.jdbc.exclusive-connection.mode"="Always"
This is probably better than the join transaction option.
James : Wiki : Book : Blog : Twitter
|
|
| |
Goto Forum:
Current Time: Sat Apr 27 16:39:28 GMT 2024
Powered by FUDForum. Page generated in 0.04371 seconds
|