Suppose a request lasts 1 second, if at that time another person sent a request, will it be fulfilled or will it wait for completion from 1 person? And the transaction locks the table for all users or only for the current one before the completion of the request?

  • It would be sad if it was expected, however for some situations it is. - Vladimir Gamalyan

1 answer 1

Each request is processed by a separate connection - i.e. parallel. If one request does not block another request, they are executed independently of each other. If there are many parallel connections, then a situation may occur when all connections are exhausted. Their number is determined by the max_connections directive (usually several hundred). If the connection is blocked - it waits when the necessary parts of the table (InnoDB) or the table itself (MyISAM) are released. At this point, the connection is not available and is serviced by other free connections. If there are a lot of locks and many threads are waiting, connections start to end and few connections remain to service fast requests. If the connections have ended at all and we have rested against the ceiling max_connections server gives new requests for new requests and simply does not process them, returning the error "Too many connections". It is impossible to increase the size of connections indefinitely, since each consumes RAM and is not small, on the order of several megabytes, depending on the settings in my.cnf.

Locks, as a rule, happen only at record and updating. The faster the requests are executed, the less time is blocked and the faster the remaining requests are executed. The faster requests are executed, the faster they release connections for other requests. Therefore, usually the main task of the developer is to achieve the fastest possible execution of requests. If there are a lot of blocking requests in the system (as a rule, for writing) - it starts to bend, the queue of waiting connections is accumulated, the RAM is exhausted, even if the blocking is later removed, the accumulated requests must still be executed.

  • And what kind of request can block another request, and how about transactions how they behave when simultaneously requesting from different users? - bsbak
  • As a rule, blocking updates and recording requests. And in the case of MyISAM, the entire table is locked. In the case of InnoDB, the section from the first record to the one being corrected. In transactions, a lot depends on what requests are in the transaction and what level of isolation is included (whether phantom reads are allowed). - cheops