Search code examples
mysqljdbclockingrowlocking

Quick explaination needed for row locking in InnoDB

I have a client-server application with 2 clients:

I need to make the reading of the database efficient when more than 1 client is reading it, without all clients getting the same rows. I use InnoDB engine for the tables, I do the queries like SELECT...FOR UPDATE and I don't use SHARED MODE

I need an explanation on how the process behaves in the following scenarios:

  1. I have 30 rows in the table. Each client has one connection object to the database
  2. Client A gets 15 rows with SELECT ...FOR UPDATE. Those rows are suppose to be locked.
  3. Client B has to get the other 15 rows that are not locked by Client A, with SELECT ..FOR UPDATE respectively.

When I test this scenario I got Client B getting also the 15 rows of Client A. Why is that happening? I set setAutoCommit(false) and never do COMMIT or ROLLBACK query, therefore the connection never commits and locks that (e.g Client A did) have been set, are never released.

Can someone point me in right direction? What I'm doing wrong?

Solution

  • I'm confused, are you trying to lock the 15 rows or not? If you are, you are missing:

    START TRANSACTION

    Here is the link to the transaction docs:

    http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html