0

I know I should be writing MAX(column) + 1 in my queries rather than MAX(column +1). However, by mistake I wrote the wrong one and it seems to prevent a deadlock issue that I was having.

Couldn't find an explanation on this after digging for sometimes. Do point me to the write direction if this is already answered or looking forward to learn some new interesting things.

1

max(column)+1 will compute max value of the column and then adds 1 to the results max(column+1) will compute max value on column value+1

Tables meta data/index will be used when you use max(column) but max(column+1) needs to scan all the rows and compute the values one by one. So good to use max(column)+1

  • So when I write SELECT MAX(column + 1) FROM table FOR UPDATE in a transaction on REPEATABLE READ isolation; what is the locking situation ? For some reason this doesn't give any deadlock errors in concurrent situation but very slow (the reason you explained earlier). But this: SELECT MAX(column ) +1 FROM table FOR UPDATE does. Any reason? – Maz Jul 23 '15 at 5:42
  • FOR UPDATE locks rows it "touches" when executing your query. MAX(column )+1 can use index over column (or similar depending to WHERE clause) so it can lock only few rows (not sure if only one or some gap etc, definitely not many). MAX(column + 1) cannot use index so well, so it has to do full table/index scan and locks "everything" - so any other query just has no way to lock anything else which might collide -> no deadlock probable, but slow execution and lot of waiting – jkavalik Jul 23 '15 at 5:48
  • @a_horse_with_no_name: The execution time is almost 10 times in MAX(column+1) than MAX(column)+1 ; so I THINK jkavalik 's answer explain the situation best. Thanks Guys. – Maz Jul 23 '15 at 5:55
  • @Maz for better understanding try checking those queries with EXPLAIN - that will tell you how it is executed and which indexes it uses. – jkavalik Jul 23 '15 at 6:19
  • Yah, did that. Interestingly enough it seems MAX(column+1) uses the index but the other one MAX(column)+1 only shows: Select tables optimized away. – Maz Jul 23 '15 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.