More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Multiple Connections from a Single Application
A single application can work with multiple Connections to the same database and assign them to different threads. The application programmer can avoid concurrency and deadlock problems in several ways:
* Use the TRANSACTION_READ_COMMITTED isolation level and turn on row-level locking (the defaults).
* Beware of deadlocks caused by using more than one Connection in a single thread (the most obvious case). For example, if the thread tries to update the same table from two different Connections, a deadlock can occur
.
* Assign Connections to threads that handle discrete tasks. For example, do not have two threads update the Hotels table. Have one thread update the Hotels table and a different one update the Groups table.
* If threads access the same tables, commit transactions often.
* Multi-threaded Java applications have the ability to self-deadlock without even accessing a database, so beware of that too.
* Use nested connections to share the same lock space.
(Continued on next question...)
