Dead lock is happening same data base record updating in multiple connection sessions concurrently

Question

We have implemented client server socket based application to process multiple shopping cart requests. Daily we receive thousands of shopping cart requests.

For this we implemented multi threaded architecture to process requests concurrently. We are using Oracle Connection Pool for data base operations and we set optimal value for connection pool size. As per our business process we have a main database table and we need to update same set of rows by multiple threads using multiple connection sessions concurrently. Now are getting some dead lock issues because of multiple threads will try to update the data on same rows using multiple connection sessions concurrently and also we are some other primary key violations on tables. Sometimes data base is also getting locked by inserting same data in multiple connection sessions concurrently.

Please suggest me good approach to handle above problems immediately.

Answer 1

There are a few different general solutions to writing multithreaded code that does not encounter deadlocks. The simplest is to ensure that you always lock resources in the same order.

A deadlock occurs when one session holds a lock on A and wants a lock on B while another session holds a lock on B and wants a lock on A. If you ensure that your code always locks A before B (or B before A), you can be guaranteed that you won't have a deadlock.

As for your comment about primary key violations, are you using something other than an Oracle sequence to generate your primary keys? If so, that is almost certainly the problem. Oracle sequences are explicitly designed to provide unique primary keys in the case where you have multiple sessions doing simultaneous inserts.