1

Suppose I have the following piece of code .

try {
   connection.setAutoCommit(false) ;
   ....
   ....


   connection.commit();
   }
 catch (Exception e)
   {
   }

Does the above transaction acquires locks on the MySQL tables which are referenced in the code in the try statement ? If it does, what kind of locks are these ? Are these read locks or write locks ? Also, does it make a row level lock or a complete lock on the table ?

Sapan
  • 59
  • 1
  • 4
  • http://stackoverflow.com/questions/3160756/in-jdbc-when-autocommit-is-false-and-no-explicit-savepoints-have-been-set-is-i – user1933888 Dec 27 '13 at 12:51

1 Answers1

1

There are two kinds of locking available in JDBC.

  1. Optimistic Locking
  2. Pessimistic Locking

Pessimistic Locking locks the records as soon as it selects the row to update.

Optimistic Locking locks the record only when updating takes place.

But connection.setAutoCommit(false); means that you are starting a transaction on this connection. All changes you will do to the DB tables in this connection will be saved on commit or reverted on rollback (or disconnect without commit). It does not mean that you lock the whole DB. Whether other users will be locked trying to access the tables your transaction uses will depend on the operations your transaction is doing and transaction isolation level.

Nidhish Krishnan
  • 20,593
  • 6
  • 63
  • 76
  • Suppose I am doing a select query from a table in this transaction and some other user tries to update the same table. Does it block the user trying to update the table ? – Sapan Dec 27 '13 at 13:18