Hi.
I know is kinda “enthusiastic “, but i’ve been tasked to deploy kanboard on sql server.
After some interesting crashes, i’ve seen some of my hand adapted plugins throw a 40001 serialization failure 1205 . Yay, thats a deadlock.
Most times, it ends working, but takes time and sometimes even crashes kanboard. My plan was to share my advances with the community on ‘fixed’ plugins once they are stable, but seems damned sql server haD other plans.
I’ve also changed the serialization to read commited manually, and requieres a misterious server full restart. EDITED (but it stubbornly fails.) EDITED
It happens in most plugins, but not in main kanboard. Whats weird itself. EDITED now seems to work fine.
Currently running on apache, php 8.3 and. 1.2.46.
EDITED: Solution: sqlserver requieres to be in read commit isolation to work. As im not sqlserver expert myself, it seems that my database server was installed with serializable isolation level.
Ill update my installation and deployment fixes to run all the plugins I use on sqlserver.
Thanks for reading!
The problem is located. Function migrateSchema on schemahandler.php y causing massive contention locks in ANY transaction due massive row lock on SQL server. It executes at an insane ratio, and causes major transaction lock (up to 25 seconds). Once disabled, even ordinary transactions speed up on frontend from 1.5 seconds (including js) to less than 500 miliseconds.
I will update this problem on the sql server adaptation error. Anyway, I think the update daemon could use a slowdown to be launched one time at hour, and would be fine. And I think it should be never running while navigating. This can be a major slowdown even in other databases engine, but I’m still investigating the issue…
1 Like
Issue and fix confirmed. A bug #5733 has been raised directly on github.
As far as I can see, this behaviour is the same for ANY engine.
-SqlLite: Pragma foreign_keys=ON / OFF
-MySql: Set_foreign_key_checks=1/0
-Postgres: Blank code (no action) (!!!)
-SqlServer: Exec sp_MSforeachtable @command (‘Alter table ? No/Check constraint all)
This is, for sure, locking down any table while the command lasts, so is causing a contention lock on all databases. Perhaps (my knowledge is limited), in sqlite and mysql the command is harmless, but in heavier databases like SQL Server this causes a huge bottleneck. And probably Postgres is not struggling because the code is directly disabled…
In my reported bug ([Bug]: Contention Locks on sqlServer/others caused by migrateSchema DB-DOS - DB optimization/fix · Issue #5733 · kanboard/kanboard) there is a suggested hotfix.
Hope this helps anyone that is having this problem.