Accessing a Database from multiple threads
[187 byte] By [JNooreza] at [2007-11-27 6:38:02] 
I would like to create a database application where more then one thread can access this database at once. What is the best way to synchronize access to it so as to prevent errors?
I take it you mean multiple threads will be updating, deleting etc at the same time. In that case you would execute your SQL statements in "Transactions". Here is an example using JSP's. The same principle works for a desktop app.
http://www.java-tips.org/java-ee-tips/javaserver-pages/executing-database-transactions.html
_helloWorld_a at 2007-7-12 18:06:34 >
I hope you realize that concurrent threads need to use different connections, so that, for example, one can commit while concurrently another rolls back.
Hippolytea at 2007-7-12 18:06:34 >
By the way the DB should handle concurrent access itself in a way to keeps the integrity of the data. Transactions should be used if you are performing multiple DML statements which depend on each other. Here is a Sun tutorial
http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html
_helloWorld_a at 2007-7-12 18:06:34 >
> I hope you realize that concurrent threads need to
> use different connections, so that, for example, one
> can commit while concurrently another rolls back.
Does the DB protect from this in the way that if one table if being updated it would hold back another update on the same table until the first is finished?
_helloWorld_a at 2007-7-12 18:06:34 >
> I hope you realize that concurrent threads need to> use different connections, so that, for example, one> can commit while concurrently another rolls back....depending on how you've set your isolation levels, not?
jverda at 2007-7-12 18:06:34 >
> Does the DB protect from this in the way that if one table if being updated it would hold back another update until the first is finished?
That's called transaction isolation. Different DBMSs achieve this in different ways,
depending on the level of isolation stipulated.
Do you enjoy the occasional dirty read ;-? http://en.wikipedia.org/wiki/Transaction_isolation_level
Hippolytea at 2007-7-12 18:06:34 >
> > I hope you realize that concurrent threads need to
> > use different connections, so that, for example,
> one
> > can commit while concurrently another rolls back.
>
>
> ...depending on how you've set your isolation levels,
> not?
I meant that if someone has screwed up and was using the same
connection in two threads, you couldn't speak of separate transactions
and the ability to commit just one of them -- there would only be one
transaction at a time on that one thread! I think we can all agree, that's a no-go.
Hippolytea at 2007-7-12 18:06:34 >
> > > I hope you realize that concurrent threads need
> to
> > > use different connections, so that, for example,
> > one
> > > can commit while concurrently another rolls
> back.
> >
> >
> > ...depending on how you've set your isolation
> levels,
> > not?
>
> I meant that if someone has screwed up and was using
> the same
> connection in two threads, you couldn't speak of
> separate transactions
> and the ability to commit just one of them -- there
> would only be one
> transaction at a time on that one thread! I think we
> can all agree, that's a no-go.
Oh, yes. Icky bad.
jverda at 2007-7-12 18:06:34 >
> Do you enjoy the occasional dirty read ;-?
> http://en.wikipedia.org/wiki/Transaction_isolation_lev
> el
Yes, but can I get dirty pictures too next time ? ,-)
>I meant that if someone has screwed up and was using the same
>connection in two threads, you couldn't speak of separate transactions
>and the ability to commit just one of them -- there would only be one
>transaction at a time on that one thread! I think we can all agree, that's a no-go.
Something I never considered. Cheers.
_helloWorld_a at 2007-7-12 18:06:34 >
Actually, I wasn't thinking of using an SQL database since I haven't studied it. But, would it be a good idea to have one object of type "Database", and use 'synchronize' to control thread access?
Also, the database access needs to be restricted based on different users, so I am using a SecurityManager. I was thinking the structure would look something like this:
security manager
--
database manager
--
database
in this way, a request to retrieve data from the database would need to be requested from my security manager. If the request is allowed, it would be filtered down to the database manager. Is this the best way to do this?
And finally, someone told me that using something like an "event queue" to access data would work but I've never tried this. My database manager thread would process the requests one at a time. would this work well?
JNooreza at 2007-7-12 18:06:34 >
Sorry, missed something:The database event queue could handle all requests; read, write, delete ...
JNooreza at 2007-7-12 18:06:34 >
Java Essentials New Topic
Java Essentials Hot Topic
Java Essentials
All Classified
- Archived Forums
- Enterprise & Remote Computing
- Java Essentials
- Desktop
- Core
- Solaris Operating System
- Security
- Database Connectivity
- Java Mobility Forums
- Web & Directory Servers
- Development Tools
- Application & Integration Servers
- Java HotSpot Virtual Machine
- Other Topics
- E-Mail, Calendar, & Collaboration
- Developer Tools
- General
- Administration Tools
- Sun Hardware
- Storage Forums
- Java Enterprise System
- StarOffice
- Open Source Technologies
- BigAdmin
- Real-Time
- Java Coding