Hello,
Should I initialize the database cursor once per each Apache thread, or should I initialize one in every function available to HTTP clients?
What happens when the client terminates the connection (i.e. user closes the browser tab)? Does the server-side function that was processing the request continue normally until it returns, or does it get terminated immediately without getting a chance to close all the transactions?
Cursors are expensive resources.
Generally...
Open them just when you need them.
Close them as soon as you are done with them.
Cursor-per-thread may tie up resources needlessly. Sometimes you have so much database activity that cursor-per-thread might be higher performance. This is rare. If you think that cursor open-close is slowing things down you need to measure the difference between per-function and per-thread to see what's really going on.
The server has no idea what is going on in the browser. Zero. Each request from the browser is a separate, unique, disconnected event.
If the browser "closes", that doesn't mean anything. It only means that the server doesn't get any further requests.
If the server was trying to download a response, then the socket crashes and the server eventually stops trying.
If the server wasn't processing anything, it's still not processing anything.
I think initializing such resources as database connection or cursor is OK per thread. Some frameworks/libraries are using thread-local "global" variables for this. Be aware of database max client connections limit. Whether to keep the db connection still open OR spend some time connecting to db for each request is up to your decision. It also depends on the database server how much resources is needed for each connection - AFAIK PostgreSQL forks new process for a connection (which is pretty expensive), while MySQL uses threads.
It depends on what web server is running your app. If someone closes browser tab, you will notice it probably when you want to write the HTTP response to the socket - it should raise write error or something like that. But I can imagine web server that is configured to kill the process/thread when detecting closed connection from client. But there are still some situations when web app gets terminated immediately - like power outage :) or forced (web) server restart.
Small hint - you should check and rollback the transaction before processing HTTP request, just for case an exception was thrown when processing last request and connection was not properly rolled back. (Or you can do this after processing the HTTP request.) Before processing HTTP request, also check that db connection is still alive (it might time-outed).