View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003576 | SymmetricDS | Improvement | public | 2018-05-23 16:12 | 2023-01-31 19:00 |
| Reporter | kstojanovska | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.9.0 | ||||
| Target Version | 3.12.22 | Fixed in Version | 3.12.22 | ||
| Summary | 0003576: Sync triggers fails with deadlock on SQL-Server | ||||
| Description | On 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. | ||||
| Tags | trigger | ||||
|
|
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. |
|
|
SQL Server has this problem |
|
|
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 |
|
SymmetricDS: 3.14 499b19e9 2023-01-31 18:44:15 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 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 | ||
| 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 |