I came across this article https://www.hellointerview.com/learn/system-design/answer-keys/ticketmaster which mentions use of distributed Redis lock to prevent double booking a seat. The article mentions it as the best solution over pessimistic/optimistic locking. However, I think introducing Redis lock also introduces network delay along with an external additional component which increases the complexity of the design. Pessimistic or optimistic locks won't have network delay. What am I missing? Is using normal database locking mechanism (SELECT FOR UPDATE) really that bad for designs like ticketmaster?
Database locks are not designed to be long-lived. They generally live for the life of a back-end transaction, not for human-time. Human time (seconds to minutes) is an eternity for a database, and thousands (millions) of those locks would devastate database performance.
Furthermore, you would need an open database connection for each user of the system, utilizing huge memory and operating-systems (locks are tied to connections/transactions). You would literally have to have a solution to route end-users back to the same application server, which has a set of open database connections so that they could re-connect to that same connection to complete the purchase. The solution just wouldn't scale, and would break under any significant load.