Search code examples
phpwebservermariadbdatabase-concurrencydatabase-locking

How database locks and web server collaborate with respect to database sessions?

This question has a wide area set, e.g. web servers, database servers, php application, etc and hence I doubt it belongs refers to stackoverflow, however since this question will help us on how to write the application code, I have decided to ask it here.

I have a confusion on how database sessions and web servers work together. If I am right, when a connection is made for a client, ONLY one session will be created for that connection, and that will last till the time either the connection is disconnected or it is reconnected due to long inactivity.

Now if we consider a web server, Apache 2.4 in particular running a PHP 7.2 application (in Virtual Host) with a database backed by MariaDB 10.3.10 (on Fedora 28 if that matters at all), I assume the following scenario (please correct me if I am wrong):

  • For each web application, right now we use Laravel, only one database connection will be made as soon as the first query is hit to the URLs it serves.
  • Subsequently, it will only have ONE database session for that. When the query is served, the connections is kept alive to be reused by other queries the application receives. That means most likely if the application receives web requests 24 x 7 continuously, the connection will be also kept alive and only will be disconnected when we restart either mysqld or httpd, that might not even happen in months.
  • Since all the users of the application, let us say something like 20 users at time, uses the same Apache servers and Laravel application files (I assume I can call that an application instance) all the 20 users will be served through the same database connection and database session.

If all the use cases mentioned above is right, then the concept of database locking seems very confusing. Since let's say we would issue an exclusive lock, e.g. lock tables t1 write;, it will block the reads and writes of the other sessions, to avoid dirty read and write operations for concurrent sessions. However, since all the 20 users uses the same session and connection concurrently, we will not get the required concurrency safety out of database locking mechanism.

Questions:

  1. How database locking, explicitly exclusive lock work in terms of web applications?
  2. Will each web request received at Laravel application create a new connection and session, or ONLY one connection and session is reused?
  3. Will each database connection have only and only ONE session at a time?
  4. Will this command shows the current active sessions or connections show status wherevariable_name= 'Threads_connected'? If it shows the current active connections, how we can get the current active database sessions?
Solution

  • Apache has nothing to do with sessions in this scenario (mostly). Database connections and sessions are handled by php itself.

    Unless you have connection pooling enabled, database sessions will not be reused, each request will open its own connection and close it at the end.

    With connection pooling enabled the thread serving the request will ask for a connection from the pool to the process manager (be it fpm or mod_php) and it will return an available connection from the pool, but there will still be at least as many sessions as concurrent requests (unless you hit any of the max_ limits). The general reference goes into more details, but as a highlight:

    Persistent connections do not give you an ability to open 'user sessions' on the same link, they do not give you an ability to build up a transaction efficiently, and they don't do a whole lot of other things. In fact, to be extremely clear about the subject, persistent connections don't give you any functionality that wasn't possible with their non-persistent brothers.

    Even having a connection pool available, the manager must run some cleanup operations before returning the conection to the client. One of those operations is table unlocking.

    You can refer to the connections reference and persistent connections reference of the mysqli extension for more information.

    However, the mode of operation you are describing where multiple client sessions share a connection is possible (and experimental) and has more drawbacks. It's known as session multiplexing.