I am trying to connect to a MS access database using a JSP. I will be having between 10-20 people acccessing this database. I was wondering if it explicitly stated each request is a transaction or does that need to be programmatically added? The datbase connection is made using a servlet, and if so does the class need to use the syncronise to make sure its only has one user accessing the database at any one time? Thanks Jai
First off, if you can use a real database instead of Access, please do. It's just not a good choice in most professional situations.
That said, I'm going to say no, you have no transactional support if you are accessing JDBC via a servlet. That means you could have half-completed transactions and many other anomolies. If you are accessing a database directly from a servlet (which is a bad practice), you need to create a UserTransaction so that if anything fails, you can roll back the entire transaction.
There's absolutely no synchronization control on the database nor should there be, the performance hit is too big. I suggest an optimistic locking solution which is basically read data, process it, and write to a table but before writing check to make sure no one else has modified this data, often using a time stamp or counter.
Using J2EE via an EJB session bean, you get a ton of transaction management for free, but it requires using a J2EE server as well as adding a lot of setup work. [ December 08, 2005: Message edited by: Scott Selikoff ]