Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple users concurrently accessing a SQL Server DB from Java

 
Ash Duckett
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there,

I am looking at writing a multi-user application in JavaFX. I've got it hooked up, after some fiddling, to a SQL Server database via Microsoft's DB driver and it's all lovely and working and I can query and stuff. My question here, though, is this: What happens if I get it up and running so that, in theory, many many users will be able to access the database via the application? Will it fall down? The setup is very simple at the moment, just using a connection string, PreparedStatements, you know, it's all been easy up to this point. Should I even worry about this at this point? Will it mean my code will have to change drastically in the future to accomodate such things? If I just make the client available to lots of people at once will it work without messing about with code? I literally just have the application and the database and a direct connection.

Even the recommendation of a book I would be grateful for!

Thanks

By the way, this is a really cool site. I recently passed my Oracle exam so I'm trying to move on and write something useful!

 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Ash welcome to the ranch

I am afraid I can't help you as i never really paid much attention to architecture, i just do what those guys tell me.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Disclaimer: I am not an expert in this area either, but I believe I got these basic things right.

If I understand it right, you're creating a new database connection to serve every user request. This solution won't scale well beyond a handful of users.

Generally, you need to use a properly configured connection pool. The connection pool will facilitate two things: it will reuse connections, reducing the number of connection attempts to the database (creating a connection is quite expensive operation, so keeping a limited amount of connections open and reusing them saves database and server resources). Secondly, it will limit the number of concurrent accesses to the database. The connection pool usually defines some upper limit on the number of connections it will try to open; if a requests comes in a situation when all connections beyond this limit are in use, the application will wait till some connection currently in use will be returned to the pool; it will be immediately provided again to service the pending request. The total limit of connections must be low enough so that the database is not overwhelmed by too many active connections (a quad-core DB server probably cannot handle dozens of connections, for example).

If you've coded your application carefully, changing the datasource from direct connection to connection pool should be quite straightforward, as closing a connection obtained from connection pool simply returns the connection to the pool. You need to restore connections to a known state before closing them: for example, when you close a pooled connection, it is returned to the pool with whatever autocommit state it has at the time. If you rely on autocommit to be true for new connections, but close some connections with autocommit set to false, this will be a problem (and a hard one to reproduce!). The same is true with all other states a connection could have, should you rely on them.

If you're using any SQL Server specific methods on your JDBC objects, this might be a problem. A pooled object - connection, statement or result set - is enclosed in a "wrapper" object which integrates the original object with the connection pool. The wrapper class is, of course, not the original class and you might have problems casting it to the desired type to call the JDBC driver specific methods. Some connection pools/JDBC drivers handle this situation gracefully (I've used only one connection pool so far and didn't have problems with that, but it's easily imaginable for a connection pool not to behave in a compatible way).

And lastly, but probably most importantly: if your database code has scalability issues, it won't fly. You need to make sure you're using PreparedStatement and properly binding parameters - this is probably the single most important point. As I don't work with SQL Server, I don't know which other things to take care of, but some best practices should be available in its documentation somewhere.
 
Tim Moores
Bartender
Posts: 3135
50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:If I understand it right, you're creating a new database connection to serve every user request. This solution won't scale well beyond a handful of users. ... Generally, you need to use a properly configured connection pool.

It sounds as if this is a desktop app, not a web app, so the concept of a connection pool is not applicable in the same way. Most desktop apps will have only a single connection to the DB at any given time, so unless you're talking of a connection pool of size 1, I don't agree that it's necessarily useful to a pool at all.

Apart from that, you should be using transactions to guard against DB failures as well as phantom reads and other such problems that become more likely the more users are accessing the DB simultaneously.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Doh! Tim, you're right, of course. Thanks.

In this case, the database server must be big enough to support all the connections (every user will have a connection of his own). The DB might need to be configured accordingly - Oracle, for example, has a "shared server" mode in which it can support many (like thousands) of concurrent connections; though only a few of them can be active simultaneously.

The application server and connection pool could still be part of the solution. I work on a project which has a Swing application launched via WebStart; this way we have a connection pool at the server and can limit the simultaneous connections to about ten, while there can be up to 200 users of the application. This was done in part because there are many other applications running on that database and having so many concurrent connections would be problematic, even with shared server mode.
 
Ash Duckett
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you so much for your input. You've set me in the right direction.

I'll be honest, I am completely new to database programming for multiple users. It was a fine moment when I got my application to talk to SQL Server and that's why I love doing this! That feeling of finally figuring out what I'm doing and making it work! Learning too, so next time it won't be such a pain to get the right connection string etc. etc.

I will look into the PreparedStatement beyond just how to use it. I will look into binding parameters also. I will also look into Transactions and introducing another layer into the architechture

My application, as it stands, literally just runs queries in PreparedStatement objects and uses ResultSets gained from that. As I say I am completely new to this stuff, but I will use what you have given me and do some further research - I think you've put me in the right direction. Thank you.

I was interested to read about your project in Swing with a connectionpool at the server. It's the server side software I need to learn about.

I will post here to let you know how I get on, or at least when the application is ready to actually be used by people bar sorting this stuff out. I will also get a book on this stuff...sorry I'm probably repeating myself now.

Thanks again.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic