How to manage concurrencies without locking tables in MySQL? In our php app, we have a table that records orders recieved at the reception. Since we can have up to 7 agents/active accounts, how is it possible to manage the concurrencies in the orders table without having to lock the table each time.

The issues are:


That before inserting any row in the main table, there must be a chain of check ups to run on the customer, etc... which impose multiple selects, then the insert.
Orders are loaded by batch, not one by one, by the agents. I fear locking the table will impose them to redo the orders' registration again due to the database forbiding access.


Should I even be worried of the writing management of MySQL or, indeed I have to procede to a locking system?
Concurrency and isolation are the two topics you are dealing with here - the ability for more than one user to use the system simultaneously and for each's work to be unaffected by the others'. RDBMS handle this through isolation levels and locks. It is how they are designed to work; it is what they do. Our job as application architects is to choose settings which meet the requirements and build the system in such a way that minimises the time window in which conflicts can occur.

In this system you want consistency between rows in the Order table and other tables. For this you need locks. This is precisely what locks are for. Don't be scared of them. They are your friend and protect your data. For the reference tables you want to ensure you are taking shared locks by only reading and not writing, and preferably row locks. This way multiple concurrent activities can share the reference data.

Writing the Order will create an exclusive lock. Hopefully on just the new row but maybe on the table. It is therefore important to release that lock quickly by committing the transaction as soon after the insert as possible.

What you do not want is that these locks are held while the agents are typing. That is an eternity in computer terms. By all means validate input as they type but only as single statements, never in a transaction spanning the whole data entry session. Then at the end, when the order is saved, start a transaction, re-read the reference data, insert the Order and commit. On any platform that should complete in milliseconds. The other operators won't even notice it happening. If there is an error or a conflict the agent will get immediate feedback, which they can correct and re-submit.

There is an alternative, which I wouldn't recommend, but list here for interest's sake.  You could architect the system on an asynchronous, message-processing design. When an agent saves data it is written to a single staging table which has one column per data value.  No validation, no normalisation. One single insert and the data's persisted. With 7 operators the chances of there even being two operations in the DB at one time are almost zero. A second process polls this table to read one row at a time. It validates and writes to the normalised schema. The result - success or failure - is recorded. Then the next row in the staging table is processed, and so on. This guarantees that all inserts are fully serialised, at the expense of a significantly more complicated design, no immediate feedback to agents and the need for additional business processes and programming to correct errors. Also this will not protect against activity from other programs connecting to the database. Something has to be reading those orders after all. As I said, not recommended.

Seven agents entering data at the same time is not a big number.  You will be able to construct a system to handle this, I promise.