DB transaction rolled back without any obvious reason/error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Following a user action failure, if there are no obvious errors in the application log related to the failed user action apart from a log trace similar to the one below, then it may fail due to inconsistencies in the database sequences used by Confluence.
1
2
2024-XX-XX XX:XX:XX,XXX WARN [http-nio-8090-exec-161] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:\n ->[PluginReadWriteTx]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #774302487)
-- url: /rest/calendar-services/1.0/calendar/events.json | userName: XXX | referer: <baseURL>/calendar/mycalendar.action | traceId: 402bf90431177240ℹ️ This example is taken from a scenario where the user can't create a Team Calendar event. Apart from this error, the application log did not log any other exceptions related to this action.
Environment
Postgres Database with Confluence Data Center
Diagnosis
When the 'SQL Logging' is enabled (or 'net.java.ao' package added with DEBUG) in {General Configuration > Logging and Profiling} menu, if the flow of SQL queries is captured like below:
1
2
3
4
2024-XX-XX XX:XX:XX,XXX DEBUG [http-nio-8090-exec-161] [net.java.ao.sql] onSql SELECT NEXTVAL('\"AO_950DC3_TC_EVENTS_INVITEES_ID_seq\"')
2024-XX-XX XX:XX:XX,XXX DEBUG [http-nio-8090-exec-161] [net.java.ao.sql] onSql INSERT INTO \"AO_950DC3_TC_EVENTS_INVITEES\" (\"INVITEE_ID\",\"EVENT_ID\",\"ID\") VALUES (?,?,?)
2024-XX-XX XX:XX:XX,XXX WARN [http-nio-8090-exec-161] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:\n ->[PluginReadWriteTx]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #774302487)
-- url: /rest/calendar-services/1.0/calendar/events.json | userName: XXX | referer: <baseURL>/calendar/mycalendar.action | traceId: 402bf90431177240And if the below logs are visible in the Postgres DB log:
1
2
2024-XX-XX XX:XX:XX UTC:XXX.XXX.XXX.XXX(38140):confluence@confluence:[30795]:ERROR: duplicate key value violates unique constraint "AO_950DC3_TC_EVENTS_INVITEES_pkey"
2024-XX-XX XX:XX:XX UTC:XXX.XXX.XXX.XXX(38140):confluence@confluence:[30795]:DETAIL: Key ("ID")=(5502) already exists.Then it means that the SEQUENCE that is used for the ID column in this example is out of sync with the actual data.
ℹ️ The above log snippets for 'AO_950DC3_TC_EVENTS_INVITEES' table is used as an example, but it can also be observed in other tables.
--
When the above errors are confirmed, please execute the below SQL queries to check the actual data and confirm the actual root cause:
ℹ️ Replace the table and SEQUENCE name according to your case.
1
SELECT NEXTVAL('"AO_950DC3_TC_EVENTS_INVITEES_ID_seq"');1
SELECT MAX("ID") FROM "AO_950DC3_TC_EVENTS_INVITEES";The values returned to the above queries should match (or should be very close - the difference should be no more than 1 or 2). If a discrepancy is observed at this point, then it is confirmed that we are affected by the issue described in this knowledge base article.
You can get further information regarding SEQUENCE by using the below SQL query:
1
2
3
SELECT *
FROM pg_catalog.pg_sequences
WHERE sequencename = 'AO_950DC3_TC_EVENTS_INVITEES_ID_seq';Cause
Confluence does not have any code flow that can interfere with the SEQUENCE values. So, possible reasons for this issue can all be on the DB side. Any DB migration or any other operation that can change the behaviour of the relevant table and the related SEQUENCE can cause such an issue.
Solution
After confirming that SEQUENCE is out of sync with the actual data, we need to set it accordingly. For that, we use the maximum value of the column that utilizes this SEQUENCE and set the SEQUENCE, max+1:
1
2
SELECT SETVAL('"AO_950DC3_TC_EVENTS_INVITEES_ID_seq"',
(SELECT MAX("ID")+1 from "AO_950DC3_TC_EVENTS_INVITEES"), true);Was this helpful?