+- Hazeron Forums (https://hazeron.com/mybb)
+-- Forum: Shores of Hazeron (https://hazeron.com/mybb/forumdisplay.php?fid=1)
+--- Forum: Updates (https://hazeron.com/mybb/forumdisplay.php?fid=11)
+--- Thread: 2022-04-06 Server SQL Deadlock (/showthread.php?tid=2726)
2022-04-06 Server SQL Deadlock - Haxus - 04-06-2022
Servers started reporting deadlocks a lot when making SQL updates. I've seen the occasional deadlock happen in the past without knowing the cause. The frequency of deadlocks seemed to increase a lot recently.
When a deadlock happens during an SQL transaction, the entire transaction fails.
The negative consequences of this event are many. One negative consequence is leaving players in limbo when they travel from one server to another, like when going through a wormhole.
A little research revealed the root cause of deadlocks during SQL UPDATE operations in PostgreSQL. The solution was trivial. It simply required a consistency of order when updating records by multiple processes. Updates are now performed in atom id order.
This requires a little time monitoring the server logs to be sure the problem is gone. So far so good. Logs are blissfully quiet.
RE: 2022-04-06 Server SQL Deadlock - Rockinsince87 - 04-06-2022
(04-06-2022, 06:49 PM)Haxus Wrote: Servers started reporting deadlocks a lot when making SQL updates. I've seen the occasional deadlock happen in the past without knowing the cause. The frequency of deadlocks seemed to increase a lot recently.
When a deadlock happens during an SQL transaction, the entire transaction fails.
The negative consequences of this event are many. One negative consequence is leaving players in limbo when they travel from one server to another, like when going through a wormhole.
A little research revealed the root cause of deadlocks during SQL UPDATE operations in PostgreSQL. The solution was trivial. It simply required a consistency of order when updating records by multiple processes. Updates are now performed in atom id order.
This requires a little time monitoring the server logs to be sure the problem is gone. So far so good. Logs are blissfully quiet.
Nice! That sounds like it would resolve quite a bit.
RE: 2022-04-06 Server SQL Deadlock - Deantwo - 04-06-2022
Maybe this will help solve the issues people have been experiencing with world rollbacks.
See: (Bug report) Rollbacks after every reboot?
and: (Bug report) City doing a MAJOR rollback.
RE: 2022-04-06 Server SQL Deadlock - Rockinsince87 - 04-06-2022
(04-06-2022, 07:15 PM)Deantwo Wrote: Maybe this will help solve the issues people have been experiencing with world rollbacks.
See: (Bug report) Rollbacks after every reboot?
and: (Bug report) City doing a MAJOR rollback.
Let's hope. That would make quite a few people happy including myself.
RE: 2022-04-06 Server SQL Deadlock - Greydog - 04-06-2022
Vassk JZKGF .. had just gone into a transition limbo just as you shut down. He is still there.
RE: 2022-04-06 Server SQL Deadlock - Tehepicdude - 04-06-2022
My avatar "Mr. Mister" (UUCWD) is stuck in limbo, I logged out on him before this update while he was generating a new system.
RE: 2022-04-06 Server SQL Deadlock - jakbruce2012 - 04-06-2022
Excellent work my man. Very glad to see further refinement of this universe.