An Interactive Relational Database Gateway with Load Balancing

Figure 3.1. Schematic view of a relational gateway following the HTTP protocol.

3.1 Single Transaction Database Sessions

Let us take a look at a simple example where a user creates a table called suppliers in the database company, and then populates the table with data. Although the following transactions make sense in the order they are presented, they are not connected - each transaction finishes execution within HTTP's single request-response pair (assuming that database username and password is sent with each transaction).

Figure 3.2. The client chooses a database (company) and an action (Insert a table).	Figure 3.3. The client fills out a table definition form.

In figures 3.2 and 3.3 the first HTTP transaction is used for providing data dictionary information, and the second transaction is used for inserting the user specified table. When the user clicks Insert Table, a result message (not shown) is returned.

Next, we might want to populate the table with some data. The user selects Insert values in figure 3.2, and presses the Prepare Transaction button again, whereupon he/she is presented with the following screens:

Fig. 3.4. The client chooses a table (suppliers) to insert values into.	Fig. 3.5. The client inserts data values (5090, Acme Suppliers Co., Littletown).

Again, the first HTTP transaction is used for providing data dictionary information. The second transaction is used for adding the data that the user has entered in figure 3.5 to the database. When Insert Values is clicked, a result message (not shown) is returned.

Finally, we want to query the table that we have just constructed. The user selects Query in figure 3.2, and clicks the Prepare Transaction button yet again, whereupon he/she is presented with the screen in figure 3.6.

As in the two previous cases, the first HTTP transaction is used for providing data dictionary information so that the user can build the query from existing tables and columns by choosing them in pop-up menus or selecting in scrolling list panes. The user also selects match conditions and matching values in the same screen. The second transaction executes the query and presents the user with the results:

Figure 3.6. The client makes a query and joins the suppliers and customers tables.	Figure 3.7. The query results are returned.

As can be seen, we can do fairly much within the confines of a single transaction. However, no interaction with the user is allowed. Let's say that we would like to use the result of the above query to further query the database. This is exactly what we will discuss in the next section.

4. Multiple Transaction Pairs - State Information Necessary

One of the advantages of the HTTP protocol is that it is comparatively fast. There are, however, disadvantages to this simplicity as well: HTTP is stateless, as opposed to for instance the FTP protocol. This means that a user always logs in "from scratch" and is only allowed one command before he/she is logged out again. The statelessness of HTTP is hidden insofar as connecting usually means retrieving a new multimedia page, and thus the user gets the illusion that state is preserved - the next time he/she connects it will be based upon the retrieval of a previous page, and the user can thus browse material as if the servers were actually preserving state. This setting was described in section 3.

But if the user would like to make a query based upon results of a previous query, those results would have to be uploaded together with the new query from the user's browser back to the database system again. The DBMS would then process the query based on comparisons with data from the first query. No wonder that most database gateways today only allow for simple queries...

However, there is one other way to go, which is surprisingly straightforward: Let the database system itself take care of state preservation for the users.

4.1 Multiple Transactions Database Session: Login

A connection with the database system is maintained by the gateway for each user, so that the user does not need to provide login information for every single HTTP transaction. When the user first logs in to the system, a unique session number is generated. The connection between the server and the browser is broken several times, but each time it is reestablished, the session number allows the relational gateway to verify the user. The gateway each time controls that the session number was resent from the same IP address, so that some security is achieved even when no encryption is used. Neither user name nor password is passed after login, so there is no additional security risk involved.

At login, a private table is created which preserves state information for the user. We call this table a VIEW since it will invariably contain derived data: If the user updates the database (by inserting or deleting tables or data) the global state of the database is changed and thus state is preserved anyway. The private VIEWs are transitional and are automatically deleted when the user logs out from the database or if he/she does not contact the database again within a preset time period (typically 10 minutes).

Since VIEWs contain derived data, their most common use is to query the database based on results from previous results (as shown below) but VIEW data can equally well be used for conditionally inserting data into or deleting data from the database.

4.2 Multiple Transactions Database Session: Repeated Queries

In a database system which preserves user specific state information, we can allow the user to repeatedly query the database using intermediate results or combinations of intermediate results with other parts of the database. This use of repeated queries is an attempt to somewhat capture the power of nested SELECTs.

When preserving personal state information, we incur a performance penalty. The extra work which has to be performed is to save the results of each SELECT statement into a view. However, we actually have to perform this step anyway, to ensure that results do not get lost when time out errors occur. The WWW-server can time out the connection or the WWW-client may not be able to reconnect to complete the transaction; both these problems have been discussed in [Bjo95/1]. In short, by providing necessary time out error handling, we get a mechanism for providing repeated queries at no extra cost.

Each time the user queries the database, a personal view is saved in the database. To use results of a previous query in a new query, we can simply join the VIEW table with other tables of the database using SELECT statements. To illustrate, we continue the example from section 3.

In section 3, we created a new table called suppliers, which has the columns supp_num, supp_name and supp_city. We then inserted a single tuple and made a SELECT. We thus have a personal VIEW, which can be accessed from all menus listing the tables of the current database. We directly continue the example and perform the following query:

Figure 4.1. Query involving a join with a VIEW table.	Figure 4.2. Results of the query, linking previous results to tables in the database.

We select the salesrep from the table orders, and join it with supp_name and cust_name from the VIEW that was generated in section 3, where orders.cust_nr and VIEW.supp_num are equal. When we click Do Query in figure 4.1 the VIEW is updated and the results (in this case that the supplier Acme and the customer Nicolo have been involved in two orders together, with two different sales representatives) are automatically displayed on-screen. We can now in figure 4.2 choose to continue working with the database, or log off, since the relational gateway is still maintaining an open connection with the database for us.

5. Load Balancing

After having implemented the relational gateway as described above, we started testing it and found that it would sometimes weaken the WWW-server's performance unnecessarily much under heavy load situations. We found two reasons for this:

1. The WWW-server is kept waiting with an open connection while SQL code is generated, database transactions execute and while HTML pages are generated.
2. The WWW-server must devote processing time to maintaining connections as well as performing message and data interchange with the relational gateway.

The number of users who can not establish a connection with the WWW-server naturally increases with load. In the worst case scenario, a particular user might be able to log in to the database and perform some initial transactions, but not be able to get a connection with the server to finish the session, since each new round of interaction with the DBMS requires a new HTTP connection to the WWW-server. The WWW illusion of interactivity then breaks down, leaving frustrated users unable to accomplish their tasks.

To distribute the load on the WWW-server, we implemented a batch mode for return of results. We specify certain criteria for the relational gateway to switch from on-line return of data to batch return by e-mail. When a switch to batch mode occurs, the user is notified and can directly continue the database session (perform other queries, etc.) or log off without having to wait. Three criteria for switching to batch mode were implemented:

(1) User choice

The user might be on a slow connection or impatient to go on with other tasks, and is offered the possibility of manually establishing batch mode. Each time a query has been built and the user is ready to send it off to the server, he/she is presented with a choice of viewing results on-screen or having it sent later by e-mail, as in the following picture:

Figure 5.1. Detail of query page.

(2) Time limit

Batch mode is automatically established to give control back to the user within a reasonable time period. The default limit is 45 seconds, since WWW-servers typically time out after 60 seconds and a few seconds are needed to notify the user of the switch.

When the time limit is reached, the user is presented with a notification that the time limit has been reached and that results will be forwarded by e-mail. The user is then returned to the home page.