I need some help regarding Table Locking and Unlocking. I am using mysql4.1 as my database and JDBC to connect to the same. Now I want to know that if I lock a table using the Connection object and then close that same connection object without unlocking the table. Will the locked table be released as soon as the connection is closed or will remain locked.
I am not sure that jdbc can lock a table. Suppose part of a transaction locks a row. If a connection is retrived from a connection pool, since close connection just returns it back to the pool, the row is not unlocked immediately. If a connection is created, closing connection will not release the lock immediately too. In either case, it's up to the database engine to release the lock.
Normally you don't lock tables (or pages or rows) explicitly; it happens implicitly if necessary. That means, if the table/page/row contains altered values within a transaction, it is locked against other changes. If the connection is closed without a commit, all changes are automatically rolled back and the locks are broken. If you return the connection to the pool without a commit or rollback after you started a transaction, that's a serious bug on your part, and you can't expect predictable behavior afterwards.