View Issue Details

IDProjectCategoryView StatusLast Update
0003576SymmetricDSImprovementpublic2023-01-31 19:00
Reporterkstojanovska Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.9.0 
Target Version3.12.22Fixed in Version3.12.22 
Summary0003576: Sync triggers fails with deadlock on SQL-Server
DescriptionOn SQL-Server, if the mssql.trigger.order.first parameter is used, it will query system tables to see if there is any existing trigger ordered as first. The query can get a deadlock with other queries on the same system table because of the isolation used on the query.
Tagstrigger

Relationships

related to 0005672 closedelong Sync triggers fails with deadlock on SQL-Server 

Activities

pmarzullo

2022-12-13 14:18

developer   ~0002246

I don't see how a deadlock can occur with trigger creation/dropping because each trigger is created in a transaction with no other commands.
Except for ingres and postgres/greenplum which use functions and triggers together, but each trigger/function pair are created singularly.

pmarzullo

2023-01-17 20:25

developer   ~0002253

SQL Server has this problem

pmarzullo

2023-01-17 20:31

developer   ~0002254

In 3.7, a feature was added to check if the trigger is first, and if not, then alter it to be first. that query in MsSqlSymmetricDialect.postCreateTrigger() is what got deadlocked. Maybe our isolation level causes that.
it uses the current transaction, but i wonder if we could add "nolock" to the SQL to change isolation level for just that query

Related Changesets

SymmetricDS: 3.14 499b19e9

2023-01-31 18:44:15

admin

Details Diff
0003576: Sync triggers fails with deadlock on SQL-Server Affected Issues
0003576
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlSymmetricDialect.java Diff File

SymmetricDS: 3.12 0c02ce06

2023-01-31 18:45:54

admin

Details Diff
0003576: Sync triggers fails with deadlock on SQL-Server Affected Issues
0003576
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlSymmetricDialect.java Diff File

Issue History

Date Modified Username Field Change
2018-05-23 16:12 kstojanovska New Issue
2018-06-05 18:32 admin Target Version 3.9.8 => 3.9.9
2018-06-21 18:55 admin Target Version 3.9.9 => 3.10.0
2018-10-03 12:52 hanes Status new => confirmed
2019-03-14 12:39 admin Target Version 3.10.0 => 3.10.1
2019-04-23 13:46 elong Target Version 3.10.1 => 3.10.2
2019-04-24 18:26 elong Tag Attached: trigger
2019-05-08 12:51 admin Target Version 3.10.2 => 3.10.3
2019-05-14 14:27 josh-a-hicks Category New Feature => Improvement
2019-06-24 17:45 elong Target Version 3.10.3 => 3.10.4
2019-08-09 19:41 elong Target Version 3.10.4 => 3.10.5
2019-10-31 14:59 admin Target Version 3.10.5 => 3.10.6
2019-11-14 19:47 elong Target Version 3.10.6 => 3.10.7
2019-12-09 15:25 admin Target Version 3.10.7 => 3.10.8
2020-01-02 14:40 admin Target Version 3.10.8 => 3.10.9
2020-01-22 19:03 elong Target Version 3.10.9 => 3.10.10
2020-02-14 13:46 elong Target Version 3.10.10 => 3.10.11
2020-03-16 20:14 admin Target Version 3.10.11 => 3.10.12
2020-05-26 12:28 elong Target Version 3.10.12 => 3.11.10
2020-06-24 20:14 elong Target Version 3.11.10 => 3.12.2
2020-07-20 13:06 elong Target Version 3.12.2 => 3.12.3
2020-08-31 19:47 elong Target Version 3.12.3 => 3.12.4
2020-09-23 15:32 admin Target Version 3.12.4 => 3.12.5
2020-11-09 13:45 admin Target Version 3.12.5 => 3.12.6
2021-01-08 20:42 admin Target Version 3.12.6 => 3.12.7
2021-02-25 13:01 admin Target Version 3.12.7 => 3.12.8
2021-03-30 19:13 admin Target Version 3.12.8 => 3.12.9
2021-04-28 14:41 admin Target Version 3.12.9 => 3.12.10
2021-05-28 11:54 admin Target Version 3.12.10 => 3.12.11
2021-07-13 12:43 admin Target Version 3.12.11 => 3.12.12
2021-11-03 12:10 elong Target Version 3.12.12 => 3.14.0
2022-07-25 16:24 elong Target Version 3.14.0 => 3.14.1
2022-08-24 18:56 elong Target Version 3.14.1 => 3.14.2
2022-10-03 14:42 elong Target Version 3.14.2 => 3.14.3
2022-11-07 13:07 elong Target Version 3.14.3 => 3.14.4
2022-12-13 14:18 pmarzullo Note Added: 0002246
2023-01-04 13:25 pmarzullo Assigned To => pmarzullo
2023-01-04 13:25 pmarzullo Status confirmed => closed
2023-01-04 13:25 pmarzullo Resolution open => unable to reproduce
2023-01-04 14:54 pmarzullo Target Version 3.14.4 =>
2023-01-17 20:25 pmarzullo Status closed => assigned
2023-01-17 20:25 pmarzullo Note Added: 0002253
2023-01-17 20:31 pmarzullo Note Added: 0002254
2023-01-31 18:43 elong Assigned To pmarzullo => elong
2023-01-31 18:43 elong Resolution unable to reproduce => open
2023-01-31 18:43 elong Target Version => 3.12.22
2023-01-31 18:43 elong Summary Resync trigger if it is failing due to deadlock => Sync triggers fails with deadlock on SQL-Server
2023-01-31 18:43 elong Description Updated View Revisions
2023-01-31 18:44 elong Status assigned => resolved
2023-01-31 18:44 elong Resolution open => fixed
2023-01-31 18:44 elong Fixed in Version => 3.12.22
2023-01-31 18:56 elong Issue cloned: 0005672
2023-01-31 18:56 elong Relationship added related to 0005672
2023-01-31 19:00 admin Changeset attached => SymmetricDS 3.12 0c02ce06
2023-01-31 19:00 admin Changeset attached => SymmetricDS 3.14 499b19e9
2023-02-15 20:25 admin Status resolved => closed