Simultaneous consultation and amendment

Question

Considering a table similar to this:

Tabela : [Frutas] 
╔════════╦═════════╦═════════╗
║ Fruta  ║   Cor   ║ Mordida ║
╠════════╬═════════╬═════════╣
║  Maçã  ║  Verde  ║   Não   ║
╠════════╬═════════╬═════════╣
║ Banana ║ Amarela ║   Não   ║
╠════════╬═════════╬═════════╣
║  Pera  ║  Verde  ║   Não   ║
╠════════╬═════════╬═════════╣
║  Uva   ║   Roxo  ║   Não   ║
╚════════╩═════════╩═════════╝

For example, 5 people can perform the consultation at the same time and "Bite" a fruit. How can I ensure that no person tries to bite the same fruit ?

(Note: If the query is performed at the same time, all people see the fruit as Not bitten at that instant, then a simple SELECT * Frutas WHERE Mordida = "Não" HAS NO EFFECT)

Answer 1

You’ve already assumed the right principle: that SELECT does not provide for the desired purpose.

It is a very common mistake, including what we see in codes here on the site: to have no change between a SELECT and the next operation. But these changes happen.

Ways to solve there are many, I will speak some of the most obvious:

UPDATE fruta SET mordido = 1 WHERE fruta = banana AND mordido = 0

The condition of the update is exactly not being bitten. No 2 operations are done, it is one. Then you see whether or not the record has been successfully changed.

Another is to create one index UNIQUE, and run this query:

INSERT INTO mordidas SET fruta = 1

The UNIQUE causes a failure if there is an attempt to insert repeated value(s).

This way, you are storing a list of bites without the risk of having two different fruits. Being UNIQUE the index, you test whether or not the insertion was successful.

Still: assuming you have several fruit plants, you can create a UNIQUE( fruteira, fruta ), that is, it can bite equal fruit from different fruit trees, or different fruit from the same fruit tree, but it cannot bite the same fruit from the same fruit tree.

Note that I mentioned two very common and easy ways to implement, but each situation deserves its own specific treatment.

What is common in most of them, is that after the attempt, you will have to show the result to the user, but then it is a matter of interface.

Colleague @Diegorafaelsouza’s comment may apply depending on the scenario:

It’s a classic transaction control problem. You need to determine what the premises are. If five people can pick the same fruit and can [or not] bite it you will have no way to stop it (in this case the last bite is the one that would be 'registered'). If picking a fruit one has to declare whether or not to bite it is another story. Or if she can pick the fruit helplessly and while trying to bite register only if the fruit is not already bitten.

Depending on the case, you will need additional mechanisms. But almost everyone can take advantage of the two examples of query above.

Example for 5 bites:

UPDATE tabela SET mordidas = mordidas + 1 WHERE fruta = 1 AND mordidas < 5

Answer 2

What you need is to work with transactions in the database. Implementation may vary depending on the DBMS you are using, but follow all the same ACID principles:

Atomicity:

All actions that make up the transaction work unit must be successfully completed in order to be effective. If during the transaction any action constituting a working unit fails, the entire transaction must be undone (rollback). When all actions are successfully executed, the transaction can be effective and persisted in the database (commit).

Consistency:

All rules and restrictions defined in the database must be obeyed. Foreign key relationships, value checking for restricted or unique fields must be obeyed so that a transaction can be successfully completed.

Isolation:

Each transaction works completely apart from other stations. All operations are part of a single transaction. The principle is that no other transaction, operating on the same system, can interfere with the functioning of the current transaction (it is a control mechanism). Other transactions cannot view the partial results of operations of an ongoing transaction (still in respect of the atomicity property).

Durability:

It means that the results of a transaction are permanent and can be undone only by a subsequent transaction.For example: all data and status relating to a transaction must be stored in a permanent repository, not liable to failure by a hardware failure.

Answer 3

Ultilizar BEGIN TRANSACTION to truncate the table and COMMIT to release it

    BEGIN TRANSACTION;
    SELECT * Frutas WHERE Mordida = "Não";
    COMMIT;