View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006716 | SymmetricDS | Improvement | public | 2025-02-05 02:24 | 2025-02-22 22:34 |
| Reporter | pbelov | Assigned To | pbelov | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.15.0 | ||||
| Target Version | 3.15.13 | Fixed in Version | 3.15.13 | ||
| Summary | 0006716: Deadlocks on Sybase due to default table locking scheme being overly restrictive | ||||
| Description | SymmetricDS uses multiple threads to update internal and customer tables. If a Sybase database server has overly restrictive locking scheme (allpages or datapages) this could result in SymmetricDS processes issuing dead-locked queries, which hurts performance (batch must be re-tried later). Solution is to update SymmetricDS class AseSymmetricDialect.createOrAlterTablesIfNecessary to enforce the "datarows" locking scheme for key tables. This will override default locking scheme set by DBA (or a lack of such setting resulting in a problematic default "allpages" scheme). | ||||
| Steps To Reproduce | Load 3 large tables. Observe at least two queries dead-locked - using the sp_lock2 built-in stored procedure. one query with column BlockedState = 'Blocked' and onether query with BlockedState='Blocking' | ||||
| Additional Information | Originally reported by Benjamin S. (and independently by Phillip M.) https://sourceforge.net/p/symmetricds/discussion/739236/thread/c6964e72ea/?limit=25 https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/X12029.htm -- Identify table locking scheme: SELECT TableName = CONVERT(VARCHAR(30), so.name), LockingScheme = CONVERT(VARCHAR(30), lockscheme(so.id)) ,case (sysstat2 & 57344) when 32768 then 1 else 0 end as bit_evaluation , crdate FROM symmetricroot.dbo.sysobjects so WHERE so.type IN ('S', 'U') and so.name like 'sym_%' order by so.name; | ||||
| Tags | dialect: sybase, performance | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2025-02-05 02:24 | pbelov | New Issue | |
| 2025-02-05 02:24 | pbelov | Status | new => assigned |
| 2025-02-05 02:24 | pbelov | Assigned To | => pbelov |
| 2025-02-05 02:24 | pbelov | Tag Attached: dialect: sybase | |
| 2025-02-05 02:33 | pbelov | Description Updated | View Revisions |
| 2025-02-05 02:35 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2025-02-05 02:35 | pbelov | Summary | Deadlocks on Sybase due to default table lock scheme being too optimistic => Deadlocks on Sybase due to default table lock scheme being overly restrictive |
| 2025-02-05 02:37 | pbelov | Summary | Deadlocks on Sybase due to default table lock scheme being overly restrictive => Deadlocks on Sybase due to default table locking scheme being overly restrictive |
| 2025-02-05 02:43 | pbelov | Note Added: 0002604 | |
| 2025-02-05 14:24 | pbelov | Description Updated | View Revisions |
| 2025-02-05 14:24 | pbelov | Additional Information Updated | View Revisions |
| 2025-02-05 15:23 | pbelov | Status | assigned => confirmed |
| 2025-02-05 15:23 | pbelov | Tag Attached: performance | |
| 2025-02-07 03:35 | pbelov | Status | confirmed => resolved |
| 2025-02-07 03:35 | pbelov | Resolution | open => fixed |
| 2025-02-07 03:35 | pbelov | Fixed in Version | => 3.15.13 |
| 2025-02-14 14:26 | pbelov | Note Added: 0002618 | |
| 2025-02-14 14:26 | pbelov | Status | resolved => closed |
| 2025-02-22 22:34 | pbelov | Project | SymmetricDS Pro => SymmetricDS |