Saturday, 16 November 2013

Inno DB roww level locking

I was hearing the term row level locking for a very long time. But I really don't know  whats that and TBH i don't even tried to know what it could be.

Nov 14 got a mail from my application Manager. Its starts with Hi Senthil " Please look in to this and Advice". I have replied to that Email saying I've asked for logs from sys-admins.

Got the logs and found that 1 Order number assigned to 2 Customer @ the same time. So now I got the issue.

Yeah I caught your mind voice. Exactly googled .... Got the solution is lock the row.

Then I started thinking how. Don't worry its easy.

i) Change your table engine to INNODB.
ii) Add transaction before start selecting the order number from the number. ( You need to have the running order number in a table, so that you can simply access it )
iii) Select query needs to be like the below

SELECT order_number_column from table_name where blah blah ..... for UPDATE;

for update will lock the row, so even on high traffice the order number will be unique.

iv) Dont forget to commit ( if success) or rollback ( if error only on error condition ) else you will end up in dead lock.

Hope you got some idea about row level locking and the use case.