What you are proposing is not really CAP Theorem unless the cluster is totally a share-nothing architecture. Please note what CAP Theorem is from my old post from Jan 01, 2013 : Consistency in ACID and CAP theorem, are they the same?
Consistency (All Nodes Have Same Data via Eventual Consistency)
Partition-Tolerance : system continues to ...
You have two unique constraints, and you say that the one on shortname causes an error only rarely.
INSERT ... ON CONFLICT ON (shorturl) DO NOTHING;
Then check if you got an error and how many rows were inserted.
if you got an error, prompt the user for a new shortname
if no row was inserted, generate a new shorturl
Then you will only get error ...
Is it possible to do this in multiple queries but still realize the same level of consistency as having performed these individual selects as a single query?
It depends on the RDBMS.
In SQL Server you can use a transaction with SNAPSHOT transaction isolation level to get a consistent view of the database across multiple queries.
The isolation level Oracle ...