In a multi-user web application like a wiki, preventing conflicting updates when multiple users try to edit the same resource simultaneously is crucial. Two primary strategies for concurrency control are optimistic locking and pessimistic locking.

Optimistic Locking Optimistic locking assumes that conflicts are rare. Instead of preventing concurrent access, it detects conflicts at the point of saving changes. This mechanism typically involves adding a (1) or a timestamp to each record in the database. When a user retrieves a record for editing, the current version number is also retrieved. When the user attempts to save their changes, the application checks if the version number in the database still matches the version number that was originally retrieved. If the version numbers (2), it means another user has modified and saved the record in the interim, and a conflict is detected. The user's update is then rejected, and they are usually prompted to refresh their data and reapply their changes. This approach minimizes the overhead of locking during reads, allowing for high concurrency.

Pessimistic Locking Pessimistic locking, conversely, assumes that conflicts are frequent and should be prevented proactively. When a user retrieves a record for editing, the system immediately places an (3) on that record in the database. This lock prevents any other user from modifying or even reading (depending on the lock type) the record until the first user releases the lock (e.g., by saving their changes or canceling the edit). A common way to implement this in SQL is using SELECT ... FOR UPDATE or LOCK TABLES. If another user attempts to access a locked record, they will either be blocked until the lock is released or receive an error message. This method guarantees that once a user starts editing, they are the sole party to modify that specific data.

Comparison

Recommendation for Web Applications For typical web applications, (4) locking is generally preferred.

Justification The preference for optimistic locking in web applications stems from several key factors:

1. Scalability: Web applications often deal with a large number of concurrent users. Optimistic locking scales better because it avoids holding database locks for extended periods during user think time. In pessimistic locking, a user editing a page might hold a lock for minutes, impacting other users and database performance.
2. User Experience: While a conflict in optimistic locking can lead to a user re-applying changes, pessimistic locking can lead to a worse user experience where users are (5) from accessing or editing content for an unknown duration. This "waiting" or "blocked" state is often less desirable than a clear conflict message.
3. Database Overhead: Pessimistic locking imposes a higher overhead on the database server. Managing and monitoring locks, especially distributed locks across multiple database instances, can be complex and resource-intensive. Optimistic locking, by simply checking a version number, is much lighter on database resources.
4. Nature of Web Interactions: Web interactions are often stateless and involve relatively short transactions. Holding locks across HTTP requests, which is common in pessimistic scenarios, goes against this stateless nature and can lead to unreleased locks if a user's session times out or they close their browser unexpectedly.

Therefore, for most modern web applications, optimistic locking provides a better balance between concurrency, scalability, and user experience, with conflicts being gracefully handled rather than proactively prevented with potentially severe performance implications.