Test the sequentiality of a column with a single SQL query
- by LauriE
Hey,
I have a table that contains sets of sequential datasets, like that:
ID set_ID some_column n
1 'set-1' 'aaaaaaaaaa' 1
2 'set-1' 'bbbbbbbbbb' 2
3 'set-1' 'cccccccccc' 3
4 'set-2' 'dddddddddd' 1
5 'set-2' 'eeeeeeeeee' 2
6 'set-3' 'ffffffffff' 2
7 'set-3' 'gggggggggg' 1
At the end of a transaction that makes several types of modifications to those rows, I would like to ensure that within a single set, all the values of "n" are still sequential (rollback otherwise). They do not need to be in the same order according to the PK, just sequential, like 1-2-3 or 3-1-2, but not like 1-3-4.
Due to the fact that there might be thousands of rows within a single set I would prefer to do it in the db to avoid the overhead of fetching the data just for verification after making some small changes.
Also there is the issue of concurrency. The way locking in InnoDB (repeatable read) works (as I understand) is that if I have an index on "n" then InnoDB also locks the "gaps" between values. If I combine set_ID and n to a single index, would that eliminate the problem of phantom rows appearing?
Looks to me like a common problem. Any brilliant ideas?
Thanks!
Note: using MySQL + InnoDB
