0

When a row is added to my table, I need a certain column's value to be max+1. This is not an auto increment situation because it's only within a set of rows within the table. In other words, when selecting certain rows, the max of THOSE rows is what needs to determine the next row I add, not the max of the entire table. So there can be more than 1 row with the same values, just not more than 1 row within my x rows I select.

This is when they are added. The values can also be changed later on, manually, via a form, and would still need to remain unique within that set (which is easy, of course).

The problem, here, is since there is a tiny delay between checking for the max value and updating it, technically, even though this has not happened in years of me using this, a second user "could" come along and get that same max value, in between the original user's select and update.

I'd rather not have to do locking the table, and the framework I am working within doesn't have transactions built in. Just curious if anyone has other ideas.

One idea I had was locking the method itself... when someone is using it, add a temporary row to the db, then check for it and if one is there, have the second user basically redirect to the same page until that row is removed. But a lot of extra db work involved there, so I don't know...

This same issue comes into play when there is a limit. Say only 50 people can add a row, 49 have added them, 2 members could both get that same 49 in between a select and update, and be allowed in.

doubl3
  • 11
  • 2
  • Try transaction in stored procedure [MySQL : transaction within a stored procedure](https://stackoverflow.com/questions/9974325/mysql-transaction-within-a-stored-procedure#9975012) this may help you. – bansi Aug 14 '18 at 03:59
  • Can you post some code? Obviously, select the "certain rows" count them, add 1 then save it in the current row. Just be careful of race conditions, which you cant really do anything about. – ArtisticPhoenix Aug 14 '18 at 03:59
  • @ArtisticPhoenix I have no problem getting the max. the problem is before I update the rows, someone else could come along and the same max be given before the first one is updated. (whether using max in the query or counting). I never have this happen because it's so unlikely someone does something that fraction of a second between select and update, but it's of course possible. – doubl3 Aug 14 '18 at 04:10
  • @bansi the built in methods in the software I am adding this for don't use transactions and plus I think the tables are myiasm. if I really, really wanted, I could still use transactions after jumping through hoops, but since they don't use them it makes me question whether I'd be going overboard to use them myself. – doubl3 Aug 14 '18 at 04:10
  • my suggestion was to use transaction inside the stored procedure, and use your non-transaction software to call the stored procedure. I don't think you can avoid race condition otherwise without some sort of locking. – bansi Aug 14 '18 at 04:19
  • @bansi Transactions don't work with MyISAM. – Barmar Aug 14 '18 at 04:28
  • @doubl3 If you're using MyISAM you can use auto-increment on a secondary column in a multi-column index. See https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html – Barmar Aug 14 '18 at 04:29
  • Locking the table is probably the only way. – Barmar Aug 14 '18 at 04:31
  • @Barmar thanks. I'll look into that. Also, technically I could do my workaround locking the method itself, sort of, but wasn't sure if that was a good way or not. it would then not be locking the actual table, just certain methods. then again.... this is very unlikely to happen, so I feel like I'd be going overboard in that situation. I didn't want to lock the table, but because of how rare this would be, maybe that is the best way.... – doubl3 Aug 14 '18 at 05:21
  • To my mind, you're confusing issues of data storage and data display. Just store the AI, and calculate the rest on-the-fly, as and when you need it. – Strawberry Aug 14 '18 at 07:15
  • @Strawberry what I'm saying is someone clicks a button to submit a form, it then needs to get the number, then another user may come along and click a button on a form before the AI number in the database ahs been updated, so they'll receive the same number from the select, since nothing prevented the second user from selecting before the database was updated from the first person's actions. Also, these aren't legit AI numbers, only in the beginning do they even increment, then can be edited manually later. – doubl3 Aug 14 '18 at 08:00
  • I assume your value is not part of the primary key, so add a unique index for that value and the set-identifier. When you update/insert, catch the error. If an error occurs (and is the key violation), just repeat the insert/update with a new value (and potentially repeat again). The same will have to be done when you manually edit the value, or how do you prevent that from happening there? (Also, depending on what the value is, you may not even need to enforce uniqueness (if you e.g. want to order by this value, you can still `order by value, primarykey`), but you are vague about this). – Solarflare Aug 14 '18 at 08:48
  • @Solarflare thanks. that may indeed be a good way to do it. As for manually editing, they edit all rows' values on the same form and I have it check when submitted to be sure it's not having any duplicates in it. So they'd still be unique. I do have another situation unrelated to this, where I am still worried, though. (where it checks a value for a permission and is supposed to cut off permissions at a certain number) – doubl3 Aug 14 '18 at 09:32
  • I'm not sure if you don't underestimate the underlying problem: if for manual editing you verify the data as a whole, you still don't prevent two people manually editing at the same time. And if you have a mechanism that prevents manually editing at the same time, why not use this mechanism to prevent inserting a new max+1 at the same time. I assume the permission cut off is something similar, and you will probably find more of those race conditions. That's what transactions (including tablelocks) shall prevent. Without, you need to take extra precautions (or ensure they don't cause problems). – Solarflare Aug 14 '18 at 10:38
  • @Solarflare in the situation where they edit them, it doesn't matter. They're edited after all rows have been added. But I realized the other problem... if editing the value for all of them, then when updating it would give an error because as it updates them one at a time it will temporarily have multiple ones with the same values in the unique index. One of the main programmers for the software I am working with said I shouldn't worry over this because he's never seen it happen where someone would sneak in during that tiny fraction of a second between select and update in other areas. – doubl3 Aug 14 '18 at 20:57

0 Answers0