March 22, 2005 at 10:54 am
I have a java application that needs to write data into a database. Currently there is one thread that sets up a prepared statement which then executes a stored procedure.
The data rate is such that I'd like to have multiple writers to the database, but I'm not sure what i have to do to make this safe on the sqlserver side. Can I set up separate threads that each set up their own prepared statements and call the one stored procedure? Is there anything I have to do to the stored procedure to make it safe to call concurrently? Or is that built in?
Any advice would be appreciated.
--Frank
March 22, 2005 at 11:54 am
A single stored-procedure can be called inherently multiple times concurrently. However, depending on what the procedure is doing it may block itself. i.e. trying to insert the same record in from multiple datasources at the same time, trying to lock the same record, etc....
As long as you are not attempting to do the exact same thing (data-wise) you should not have any problems.
However, ALWAYS test and re-test and test some more
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 22, 2005 at 12:31 pm
the stored procedure makes an sql updates call to a specific table. The table itself has a fixed number of entries. The application simply reads a datafeed that updates these entries. but it does so at very quickly.
I fully expect it to lock specific rows as it is possible for multiple updates to the same row. That is fine. Most of the time updates are to different rows within the same table which is why i think we can benefit from multiple threads.
So, if it's safe to call the stored procedure at any time, then my next question concerns the prepared statement. What is the proper way to use a prepared statement across multiple threads?
--Frank
March 22, 2005 at 1:40 pm
>>What is the proper way to use a prepared statement across multiple threads?
What are you using for data access. Since it's Java, can we assume JDBC ? If so, are you using the JDBC-ODBC bridge which is not thread-safe ?
March 22, 2005 at 2:23 pm
Yes - on both counts. However I was not aware that the DriverManager is not thread safe - i'll have to read up on it.
So, regardless, what is the preferred method?
I'm about to try setting up a DSN that allows connection pooling and just spin off say 15 or so threads that make a connection, write the data then close the connection. But if the connection via Driver Manager is not thread safe - well - what is the proper way?
--Frank
March 23, 2005 at 7:39 am
in win32 with MSSQL I use critical sections to ensure no two threads talk to the database at the same time on the same connection. Without this I get hsmt busy error on MSSQL. If I want one thread to never have to wait, then I give that thread it's own connection (odbc).
I dont know how this translates for you but works well in my world.
Jim Kane
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply