How not to exceed the maximum allowed number of Photos in a database table with the same albumID?

Question

Database design

I apologize for the poorly conveyed "essence of the question".
There are entities Album and Photo.
Photos are not stored in the album collection, but separately. The photo contains the album id (albumID), this is the only link between the album and the photos.
There is a task: not to exceed the number of photos in the album. That is, you need to ensure that photos that, for example, have albumID === 123, do not exceed a hundred in the database. And such an outcome is possible in the following scenario: there are already 99 photos in the album, 2 requests are being processed in parallel to add photos to this album. First, the first process sees that the number of photos is 99 and creates a new Photo. Then the second process checks that the number of photos is still 99 (the first process has not saved the photo yet) and also creates a new Photo, then the processes in turn save the created photos to the database and it turns out that there are already 101 photos in the album.
(It is worth noting that I want a photo to be created in this use case, but no changes to the album, that is, Album::photosCount will not be incremented).
Optimistic and pessimistic blocking will not help here, because nothing changes, only it is created. It can help to disable reading the number of photos so that the second process waits until the first process finishes so that it can read the updated number of photos, but I don’t know if this is correct. Or somehow make a limitation in the database so that there are no more than 100 identical values ​​in the "albumID" column in the table with photos.
I did not find a solution on the Internet, probably because basically everyone uses collections and lazy load, in which case it is very easy to observe invariants. But for large projects, this approach is not appropriate (seemingly) because of the problems associated with increasing the number of records and scaling. Naturally, I do not need it now, I just want to know the optimal solution.

Answer 1

This is done using the business logic of the application. A database-level solution won't work here

Answer 2

How not to exceed the maximum allowable number of 100

Do not write down the 101st. For example, a composite unique key: код альбома, номер. Number sequentially. If it is added at the same time, then whoever completes the transaction first, that and slippers - another mistake.

But it's not that simple

Search for a missing value .