atomic DB transactions, other multiple frontend stuff

[binary1230]

was thinking today about where uber might break if we setup multiple frontends talking to the same database.

for example in shift_badges():

            for a in self.query(Attendee).filter(Attendee.badge_type == badge_type,
                                                 Attendee.badge_num >= badge_num,
                                                 Attendee.badge_num <= until,
                                                 Attendee.badge_num != 0):
                a.badge_num += shift

My understanding here is that this would be OK because it's part of an automatic transaction created by session.begin() and closed automatically by session.commit(), so by default everything is wrapped in one transaction, which is good.

I guess the other thing we would need to do is rip out c.BADGE_LOCK when it's used to lock down the database because that only guarantees atomic operations on that particular frontend will be locked, and not all frontends. Like here:

       def match_to_group(self, attendee, group):
            with c.BADGE_LOCK:
                available = [a for a in group.attendees if a.is_unassigned]
                matching = [a for a in available if a.badge_type == attendee.badge_type]
                if not available:
                    return 'The last badge for that group has already been assigned by another station'
                elif not matching:
                    return 'Badge #{} is a {} badge, but {} has no badges of that type'.format(attendee.badge_num, attendee.badge_type_label, group.name)
                else:
                    for attr in ['group', 'paid', 'amount_paid', 'ribbon']:
                        setattr(attendee, attr, getattr(matching[0], attr))
                    self.delete(matching[0])
                    self.add(attendee)
                    self.commit()

Counter-intuitively, removing the lock there and doing nothing else might be fine becuase there are some modes in postgres for concurrent access (that I haven't fully explored) which have really nuanced things that happen if the data under you changes in the middle of a transaction:
http://www.postgresql.org/docs/9.1/static/transaction-iso.html

I guess the other thing we'll need to do is put the session data in the DB itself, shared by all frontends.

[binary1230]

new thing to add to this list: storage of images/documents. currently some plugins (like bands, mivs, etc) store images/etc on the filesystem itself.

we may have to rethink this approach, perhaps storing these as binary data in the DB, or we will have to take into account the fact that we need to replicate this data around, or have some kind of centralized storage common to all frontend instances.

I personally think the ideal thing would be to store it in the DB for purposes of backup / replication / moving data from cloud to physical servers, but not aware of any performance implications of that.

all stuff to consider later on.