What happens to a database session when the user closes the browser tab/windows before the transaction is completed?

Question

In a webshop scenario where for example a user goes on to press "buy" on his shopping cart, what happens in case he closes his browser window or tab down?

is the database transaction that for instance updates the quantity of the product in question or a database trigger fired in response and logs the details of the transaction, rolled back or do they continue to completion?

in other words, is a "system" able to determine that the user has closed his browser willingly or by accident, like there's been power loss or the internet connection gone down, and terminate or cancel his transaction in response?

How do things work in real world environments?

Answer 1

The behavior one can observe in situations like this depends not just on the database, but also on the architecture of the web application and its backing service(s).

In evaluating situations like this, it is important to consider the multiple pieces in play -- generally there is at least one (and often two or more) layers between a shopper using a web application in the browser and the backing database.

If we start with the simplest case, (pretending there is no web app and the shopper is directly connected to the database) the answer is yes. Oracle's internal monitors and the drivers responsible for connecting and disconnecting are very good at detecting when a client disconnects with uncommitted changes and rolling things back. This is an important feature in any database server, as many clients may need to update the same data (such as purchasing items in a retail setting) in a short time-frame, and any kind of lost, hopeless, permanent locks on data would seriously disrupt the database's ability to deliver concurrent data access. I'll put an example below to illustrate this, but there is tons of related documentation. Processes in the concepts manual has a lot of good related information.

Pretending we have no web app and just connect via sql plus, etc. We can see this detection in action.

Let's create a test table:

CREATE TABLE PRODUCT(PRODUCT_ID NUMBER,QUANTITY_REMAINING NUMBER);

Table created.

And give it some records:

INSERT INTO PRODUCT VALUES (1,100);
INSERT INTO PRODUCT VALUES (2,1);
COMMIT;
1 row created.
1 row created.
Commit complete.

Now let's get two sessions started by different users. Both of these sessions want to purchase item 1. Session A gets there first.

Session A:

UPDATE PRODUCT
SET QUANTITY_REMAINING = QUANTITY_REMAINING - 1
WHERE PRODUCT_ID = 1; 

1 row updated.

Then, before Session A exits unexpectedly, Session B also tries to buy product 1. She must wait until Session A is done.

Session B:

UPDATE PRODUCT
SET QUANTITY_REMAINING = QUANTITY_REMAINING - 1
WHERE PRODUCT_ID = 1; 

Notice no "1 row updated." here.
Now I'll remotely kill Session A (pid 8771 in this example) It has no chance to commit or rollback, or exit normally, disconnect, etc.

% kill -9 8771 
zsh: killed     sqlplus /nolog

Now Session A is gone, but if we look at Session B, we can see it immediately was able to proceed:

1 row updated.

So yes, Oracle process monitoring can handle abnormal client disconnects just like normal client disconnects and rolls back ok.

But that is only a small part of the story... in typical web applications, there is (at least one) very long-lived connection(s) in a connection pool managed by the web container. To the database, it doesn't matter whether the connected client is an individual user or a huge web app, it handles unexpected disconnections the same.

But in a web app, the user doesn't control the web-app's database connections--the user interacts with the web application via their browser that may in turn make additional calls to another service application that is communicating to the database. The user in their browser has no idea what data is being updated or how, just that they want to put something in the shopping cart. They have no control over what the web application does with its database connections.

With these additional pieces in play the answer depends on the application architecture and design (not the database). Often if a user has sent their request to put something in the shopping cart(or to complete an order) and then disconnects unexpectedly before getting a response back, their request will be completed by the server nonetheless, but they just won't be there to get the "ok" response back.

The application developer (or web framework) will then have a decision what to do with the in-cart item (that already successfully saved to the database) that may be waiting on the missing shopper indefinitely.

Many application developers (and web frameworks) build-in measures to help clients keep a clear awareness of the state of their requests (for example the popup preventing the tab or browser exit with "Are you sure you want to lose an incomplete order?" and the like). Or will build in monitors to "rollback" stuff that has been in a shopping cart but not actually purchased after a while, or will send an email to confirm when a purchase actually completed (in the event the power went out just after clicking "buy") etc. etc. But that is up to the application developer to decide.

To summarize, yes Oracle database can detect incomplete transactions in the event of a client power outage or the like and rollback just fine. With web applications there are different designs and tech in play to meet the different needs of clients going missing at different times and provide a understandable and satisfying user experience.