[YSQL] Postgres compatible transaction semantics

[rkarthik007]

Jira Link: [DB-326](https://yugabyte.atlassian.net/browse/DB-326)
This is a master issue tracking tasks to ensure user-visible transaction semantics in YSQL are the same as that of Postgres to the extent theoretically possible (i.e., barring any differences that stem from distributed nature of the database).

There are also some tasks/issues that don't exactly fall in the bucket of user-visible transaction semantics but are related to the transactional sub-system.

Phase 1 - Miscellaneous items

• [YSQL] Support locking options in SELECT statement (SELECT FOR UPDATE etc.) #1199
• [YSQL] Transparently restart single statement write transactions #4291
• [YSQL] Automatically handle read restarts in YSQL when possible #1237
• [YSQL] Transparently restart first statement in txn when possible #9467
• [YSQL] Explicit row-level locking should only lock what is returned to the client #9929
• [YSQL] Support SKIP LOCKED for explicit row-level locks in REPEATABLE READ isolation level #2742
• [YSQL] Support SELECT ... NOWAIT in READ COMMITTED isolation level #11760
• [YSQL] Support SELECT ... NOWAIT in REPEATABLE READ and SERIALIZABLE isolation level #12166
• [YSQL] Support SKIP LOCKED for explicit row-level locks in SERIALIZABLE isolation level #11761
• [YSQL] Support Pg table-level locks #11571
• [YSQL] Support observability of actively held and contended locks via pg_locks #12168
• [YSQL] Test fk-contention in isolation regress suite fails (the test hasn't yet been added) #12325
• [YSQL] Bad behavior in PL/pgSQL block statement with exception section on 'unique_violation' (code 23505) #12184
• [YSQL] ON CONFLICT UPDATE on partitioned primary key column crashes #14985
• [YSQL] SELECT FOR KEY SHARE failing on non-key column update in partitioned table #14991

Phase 2 - READ COMMITTED isolation level (present as a beta feature in 2.14 and 2.15)

• [YSQL] Support READ COMMITTED isolation level #13557 (detailed roadmap present in the github issue)
• [YSQL] Make read-committed the default isolation level in YSQL #15272

Phase 3 - Wait queue based pessimistic locking

This feature will support pessimistic locking behaviour for all isolation levels (READ COMMITTED will already have a basic implementation of this in Phase 2, this will serve as an optimization for READ COMMITTED isolation level and will get rid of the basic implementation)

• [YSQL] Support wait-on-conflict concurrency control #5680 (detailed roadmap present in the github issue)

This has been asked in different ways:
Issues from @RunningJon - #9465, #9469
Issues from @bllewell - #1985

Phase 4 - Possible future enhancements

• [YSQL] Use predicate locks instead of coarser locks in SERIALIZABLE isolation level #9517 (Required for SKIP LOCKED in SERIALIZABLE isolation level. Also allows for higher concurrency than coarse table/tablet locks)
• [YSQL] Explore Serializable Snapshot Isolation (SSI) for concurrency control in YB's SERIALIZABLE isolation level #9518