Consultation and simultaneous change

You have already started from the right principle: that SELECT is not suitable for the desired purpose.

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

Ways to solve for many, I'll mention 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. Two operations are not done, it is one. There you see whether or not the record has been successfully changed.

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

INSERT INTO mordidas SET fruta = 1

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

In 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 succeeded.

In addition: assuming you have several fruit trees, you can create a UNIQUE( fruteira, fruta ) , that is, you can bite the same fruits from different fruit trees, or different fruits from the same fruit tree, but you can not bite the same fruit from the same fruit tree. p>

Please note that I mentioned two very common and easy-to-implement ways, but each situation deserves its 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 there is interface.

The comment from colleague @DiegoRafaelSouza may apply depending on the scenario:

  

This is a classic transaction control problem. You need to determine what the assumptions are. If five people can pick up the same fruit and can [or not] bite them you will not be able to prevent it (in which case the last bite is the one that would be 'registered'). If you pick a fruit, you have to state whether or not you will bite it, it's another story. Or if she can pick up the fruit indefinitely and try to bite to 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 query examples above.

Example for 5 bites:

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

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

Atomicity:

All actions that make up the work unit of the transaction must be successfully completed for it to take effect. If any action that constitutes a unit of work fails during the transaction, the entire transaction must be rolled back. When all actions are successful, the transaction can be committed and committed.

Consistency:

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

Isolation:

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

Durability:

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