Managing simultaneous form submissions

Anvil Q&A
#1

What I’m trying to do:
I am trying to create a table that automatically increment every time the user submit a form to keep track of the submissions.
What I’ve tried and what’s not working:
I tried using the following codes. But found that when 2 people submit at the same time, the IDs are the same. What should I do to avoid this? Thanks.
Code Sample:

    max_value = app_tables.id.search(tables.order_by("ID", ascending=False))[0]['ID']
    #print(int(app_tables.id.search()[0]))
    #print(app_tables.id.search(tables.order_by("ID")))
    self.ID=max_value+1
    app_tables.id.add_row(ID=self.ID)

Clone link:
share a copy of your app

#2

Look up “Transactions” in the Docs. They’re designed to address this problem head-on.

Reading, incrementing, and applying such a database-global value should be done in a Transaction, to prevent exactly this kind of problem. The entire sequence of database operations must complete as a whole.

1 Like
#3

Thank you for your reply. I am aware of this. The reason I need to create and ID is because I a separate table to render multiple attachment files. I want to have an ID to link the attachment files with the main table.

#4

If you’re aware of, and using, Transactions, then how are you getting duplicate IDs?

Edit: scratch that. The technique you’re using is not reliable. You don’t have a single, unique sequence number field. You’re relying on a transient, computed value, the one in the highest-numbered row. The problem is that, by the time you’re done, another user may have made a different row the highest-numbered-one, and so your code is no longer using the right (in-database) variable.

The sequence-number needs to be its own, unique, independent data item, stored in a single, unique row, and updated under transaction control.

Think of it as a “global variable” persisting in the database, which keeps the sole, master copy, and which anyone can update, at any time. It’s up to you, then, to wrap all such uses in a Transaction, so that other users have to wait their turn while you’re using it.

For my purposes, instead of sequential numbers, which have to be guarded this way, I prefer to use large random numbers or strings. (If it’s a Number column, make it 52 bits or less, to fit in the significant part of a float.) The odds of two different users producing the same ID, in this case, are so small, they can usually be ignored.

#5

If you only need an identifier (vs an actual row counter), you can use my_row.get_id().

1 Like
#6

Use a different self ID. You can get the row ID at anytime aftet entry but much better to create a primary key using something else at the start. For example, you might want to combine an input field and timestamp plus a random string and shuffle the lot, then check if that random string exists before committing. You could use UUID but i dont personally like it. You won’t then experience the issue you are facing ever again.