kv: disallow non-transactional locking requests

**Describe the problem**

Non-transactional requests are not allow to acquire locks. However, we do allow non-transactional read requests to have a locking strength associated with them. They use this locking strength to declare lock spans and perform conflict resolution during their scan of the lock table. They also use this locking strength to check which keys to skip over (or not to skip over) when running with the `SKIP LOCKED` option.

We should reject non-transactional requests that have an associated locking strength. They aren't used by SQL and there's very little benefit in supporting them. On the flip side, rejecting them reduces the testing space we need to account for in the lock table. It also allows us to tighten some assertions we removed in https://github.com/cockroachdb/cockroach/pull/109610.

Jira issue: CRDB-31020