I have my web application and I am having statistics about my users in json type column. For example: {'current': {'friends': 5, 'wins': 2, 'loses': 10}}. I would like to update only specific field in case on race condition. For now I was just simply updating whole dictionary but when User will play two games at the same moment, race condition could occur.
For now i am doing this like that:
class User:
name = Column(Unicode(1024), nullable=False)
username = Column(Unicode(128), nullable=False, unique=True, default='')
password = Column(Unicode(256), nullable=True, default='')
counters = Column(
MutableDict.as_mutable(JSON), nullable=False,
server_default=text('{}'), default=lambda: copy.deepcopy(DEFAULT_COUNTERS))
def current_counter(self, feature, number):
current = self.counters.get('current', {})[feature]
if current + number < 0:
return
self.counters.get('current', {})[feature] = current + number
self.counters.changed()
but this will update whole counters column after changing value and if two games will occur I am expecting race condition.
I was thinking about some session.query, something like that, but I am not that good:
def update_counter(self, session, feature, number):
current = self.counters.get('current', {})[feature]
if current + number < 0:
return
session.query(User) \
.filter(User.id == self.id) \
.update({
"current": func.jsonb_set(
User.counters['current'][feature],
column(current) + column(number),
'true')
},
synchronize_session=False
)
This code produce: NotImplementedError: Operator 'getitem' is not supported on this expression for Event.counters['current'][feature] line but I don't know how to make this works.
Thanks for help.
The error is produced from chaining item access, instead of using a tuple of indexes as a single operation:
User.counters['current', feature]
This would produce a path index operation. But if you would do it that way, you would be setting the value in the nested JSON only, not in the whole value. In addition the value you're indexing from your JSON is an integer (instead of a collection), so jsonb_set() would not even know what to do. That is why jsonb_set() accepts a path as its second argument, which is an array of text and describes which value you want to set in your JSON:
func.jsonb_set(User.counters, ['current', feature], ...)
As for race conditions, there might be one still. You first get the count from the current model object in
current = self.counters.get('current', {})[feature]
and then proceed to use that value in an update, but what if another transaction has managed to perform a similar update in between? You would possibly overwrite that update's changes:
select, counter = 42 |
| select, counter = 42
update counter = 52 | # +10
| update counter = 32 # -10
commit |
| commit # 32 instead of 42
The solution then is to either make sure that you fetched the current model object using FOR UPDATE, or you're using SERIALIZABLE transaction isolation (be ready to retry on serialization failures), or ignore the fetched value and let the DB calculate the update:
# Note that create_missing is true by default
func.jsonb_set(
User.counters,
['current', feature],
func.to_jsonb(
func.coalesce(User.counters['current', feature].astext.cast(Integer), 0) +
number))
and if you want to be sure that you don't update the value if the result would turn out negative (remember that the value you've read before might've changed already), add a check using the DB calculated value as a predicate:
def update_counter(self, session, feature, number):
current_count = User.counters['current', feature].astext.cast(Integer)
# Coalesce in case the count has not been set yet and is NULL
new_count = func.coalesce(current_count, 0) + number
session.query(User) \
.filter(User.id == self.id, new_count >= 0) \
.update({
User.counters: func.jsonb_set(
func.to_jsonb(User.counters),
['current', feature],
func.to_jsonb(new_count)
)
}, synchronize_session=False)