View Revisions: Issue #6716

Back to Issue
Summary 0006716: Deadlocks on Sybase due to default table locking scheme being overly restrictive
Revision 2025-02-05 02:24 by pbelov
Description SymmetricDS uses multiple threads to update internal and customer tables.
If a Sybase database server has overly optimistic 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 AseDdlBuilder to create all tables with the locking scheme set to "datarows" explicitly. This will override default locking scheme set by DBA (or a lack of such setting resulting in a problematic default "allpages" scheme).
Revision 2025-02-05 02:33 by pbelov
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 AseDdlBuilder to create all tables with the locking scheme set to "datarows" explicitly. This will override default locking scheme set by DBA (or a lack of such setting resulting in a problematic default "allpages" scheme).
Revision 2025-02-05 02:24 by pbelov
Steps To Reproduce Load 3 large tables.
Observe at least two queries dead-locked - using the sp_lock2 built-in stored procedure.
  with column BlockedState = 'Blocked'
  and one query with BlockedState='Blocking'
Revision 2025-02-05 02:35 by pbelov
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'
Revision 2025-02-05 14:24 by pbelov
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).
Revision 2025-02-05 02:24 by pbelov
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
Revision 2025-02-05 14:24 by pbelov
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;