I have a requirement where I need to do CRUD operations on a single database (SQL SERVER). I need to do this mainly to keep a track of what was the last SOAP request that was sent by a client to the server. Since I have to keep track of only the latest request, I have a table with 2 columns (USER_ID, REQUEST). Lets assume the REQUEST is in String format.
The above requirement implies that each client connecting to the server will access its own row defined by the USER_ID of the client and will not intrude into other rows. Every client should be able to do CRUD on his own row only. However the number of clients could go upto 10000, which means there could possibly be 10000 rows worst case in this DB table.
I am new to Threads and synchronisation and have heard about synchronised JAVA code to handle CRUD on a database and also about database isolation levels. Can anyone let me know whether any kind of synchronisation is required at the JAVA level or the DB level? If it is required at the JAVA level that what parts of the java code need to be synchronised and which do not need to be?
How should connections be handled in such a case? Since I am working with a couple of tables I need to do very small queries like updating a single column and querying a single row. Is it advised to use the DBCP BasicDataSource to minimise the connection creation effort. For a client base of 10000+ users, what could be the connection pool parameters like the MinIdle, MaxIdle, MaxActive and other special parameters? What impact could each have on the performance of the system?