0

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?

Radu Murzea
  • 10,724
  • 10
  • 47
  • 69
Regs
  • 119
  • 1
  • 7

1 Answers1

1

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

Eric Leroy
  • 1,830
  • 1
  • 18
  • 31
  • 1
    As he is using JDBC, he should set use `setAutoCommit(false)` on his connection instead. The JDBC driver is responsible for transaction management as internally there are dependencies on transaction state as well, and issuing `START TRANSACTION` like that could bring the driver in an inconsistent state. – Mark Rotteveel Oct 20 '12 at 07:43