Database engines include built-in mechanisms for transaction management. The application can begin a transaction and have it committed or rolled back. The database engine will ensure that uncommitted data will not be visible to other users.
Web applications cannot take advantage of transaction managers built into database engines. To ensure scalability, web applications maintain a pool of database connections shared by multiple users simultaneously. Transaction managers require that all modifications of data are performed in the context of a single open database connection. A typical web application may handle requests to modify data from a single user by utilizing multiple pooled connections.
Developers can implement client-side caching of requests to update data. The application user modifies several data items on the client and requests to submit changes at once. The entire batch of modifications is sent to the web application server for processing. The server code will parse the batch and perform individual updates using a database transaction.
Users will have to stay on the same web page until the data is submitted. There is a potential for data loss if a browser window is accidentally closed. The data entry sessions must be short.
A substantially more flexible solution is to “stage” data on the server. Data records are submitted straight to the database tables, and marked as “Draft” using various techniques. When the draft data is ready to be included in the main dataset, the status of the data rows is changed to “Committed”.
Users can submit draft data from multiple pages. The data is safely stored in the database instead of volatile browser cache. The drafted data may be perfected over any period of time from multiple devices.
Application developers can take full advantage of SQL when manipulating draft data. Code On Time web applications include mechanisms that make data staging implementation trivial.
Suppose that you have created an Order Form web application from the Northwind sample database. Each order is stored in two tables: dbo.Orders and dbo.[Order Details]. When a user wants to create a new order, they must first create the order record, and then add a few order details.
The user should have the opportunity to add or change details, or drop the order altogether before committing it to the primary data set.
However, these “draft” orders will be included in all data sets, such as Order Subtotals. It is necessary to segregate the draft order data from the primary data set.
Consider the following methods of draft data staging when implementing web transactions.
The first two methods will use dynamic access control rules in order to prevent committed data from being displayed on the order form, and draft data from being displayed on all other pages.