Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
2022-04-06 Server SQL Deadlock
#1
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.
Reply
#2
(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.
Avatars: - LimboWarrior

[Image: ezgif-com-resize.gif]
Reply
#3
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.
Hazeron Forum and Wiki Moderator
hazeron.com/wiki/User:Deantwo
Reply
#4
(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.
Reply
#5
Vassk JZKGF .. had just gone into a transition limbo just as you shut down. He is still there.
I plan on living forever ..so far so good!
Reply
#6
My avatar "Mr. Mister" (UUCWD) is stuck in limbo, I logged out on him before this update while he was generating a new system.
Reply
#7
Excellent work my man. Very glad to see further refinement of this universe.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)