How does optimistic locking with the version field work? Explain in detail with an example of two transactions.

Optimistic locking with a version field is a concurrency control strategy where each database record contains a version field (e.g., an integer). When updating a record, the transaction checks that the record's version has not changed since it was read and then increments it.

Example with two transactions:

1. Transaction A reads a record with version = 1.
2. Transaction B also reads the same record with version = 1.
3. Transaction A modifies the data and attempts to save the record, checking that the version is still 1. The update succeeds, and the version is incremented to 2.
4. Transaction B tries to save its changes, checking that the version is still 1. But the version has already become 2 after A's update.
5. The update by B is rejected (usually an exception is thrown) because the data is outdated.

Thus, optimistic locking allows avoiding conflicts without locks, assuming collisions are rare. If a conflict occurs, the transaction should be retried or canceled.

This ensures data integrity during concurrent modifications, preventing loss of updates.