The problem is that this only helps if one particular account is attacked. But instead of trying 100 passwords on one account, an attacker might also try one common password on 100 accounts. To prevent this, you also have to monitor the total number of failed logins and start displaying CAPTCHAs when the number gets exceptionally high.
For user-friendliness, the failed logins should expire automatically after a while. It doesn't make sense to keep the counter for more than, say, 24 hours.
Last but not least, be careful with race conditions when you actually implement this. Many people first check the number of failed logins and then increment the counter if needed. But this allows an attacker to send an arbitrary number of concurrent requests between the check and the increment, and each time the server will still “see” the old counter. You need a single query where you increment the counter and get the new value at the same time, and then you have to check that value. Different database systems have different techniques for this. In PostgreSQL, you can use an
UPDATE query with a
RETURNING clause. In MySQL, you can use an
UPDATE query with the increment expression wrapped in
LAST_INSERT_ID() and then get the incremented value with
SELECT LAST_INSERT_ID(). Note that you can't just make a plain
SELECT query after the update, because the counter might already have increased in the meantime.