[Docs] Revamp concurrency control docs

[pkj415]

Description

Some history:

As per #23580, we cleared the use of optimistic locking and pessimistic locking from our docs pages and instead switched to naming them Fail-on-Conflict concurrency control (FoC) and Wait-on-Conflict concurrency control (WoC) respectively. FoC is the older YB policy and WoC is the newer policy that matches Pg semantics exactly and is also YB's default going forward. The renaming was done because pessimistic locking wasn't an apt name to describe WoC. People usually use pessimistic locking to refer to the SELECT FOR UPDATE pattern specifically. We were using it to describe the whole concurrency control policy. And optimistic locking was also an incorrect name for FoC because it really refer to "Optimistic concurrency control" which is something totally different (https://en.wikipedia.org/wiki/Optimistic_concurrency_control) and not at all FoC.

Recently, there have been instances when people tried searching for pessimistic locking in our docs and didn't find anything. This might lead to the assumption that we don't support the SELECT FOR UPDATE pattern, but the fact is that we actually do support it (exactly the same way as Pg, when using WoC). With FoC, the pattern has slightly different semantics than Pg, which was leading to the conclusion in old times, that YB doesn't support pessimistic locking, which was apt. So, what likely happened was the following: YB not supporting SELECT FOR UPDATE => people started saying YB doesn't pessimistic locking, which is correct => we needed to build machinery to support wait queues to support pessimistic locking => we named the whole wait-queues model pessimistic locking, which applies to everything and not just select for update.

We need to revamp our docs pages:

1. Modification to https://docs.yugabyte.com/stable/architecture/transactions/concurrency-control/:

We have two conflict resolution policies in YB: Fail-on-Conflict (FoC) and Wait-on-Conflict (WoC). However, these are internal names and not used outside YB. Also, we had to bring in this naming to differentiate YB's old FoC policy (which isn't really used in other DBs) from the newer WoC policy (which is same as Pg, and is now YB's default). We should align our docs pages to really remove this distinction since it is not important. YB will be using WoC going forward by default (I think since 2024.1) and FoC will be a thing of the past. So, we should instead just mention that YB's policy is the same as Pg, and have an FoC section for book-keeping the old behaviour and in case anyone chooses to revert in future.

2. People usually refer to the usage of SELECT FOR UPDATE as pessimistic locking. Add that alongside a SELECT FOR UPDATE example in https://docs.yugabyte.com/stable/architecture/transactions/concurrency-control/. Note that we were earlier calling the whole of WoC as pessimistic locking and the whole of FoC as optimistic locking.

3. Check for mentions of WoC/FoC at other places in the docs, and clear those.

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.