Auto incrementing a non-unique id upon creation using SQLAlchemy

Auto incrementing a non-unique id upon creation using SQLAlchemy

My primary goal with this is to make implementing revision histories and
journaling easier.
I found myself wondering if it's possible, using Flask-SQLAlchemy (or just
straight up SQL), to get an auto incremented non-unique integer for mysql.
I found this stack overflow post which is close to what I want to do but
the question is focused on a primary key. For example, if my table had
these columns,
revision_id = db.Column(db.Integer, nullable=False)
post_id = db.Column(db.Integer, nullable=False)
__table_args__ = (
PrimaryKeyConstraint('post_id', 'revision_id'),
)
Would it be possible to create a new post that has a revision_id of 1 and
a post_id of max(post_id) + 1 without the problem of two users attempting
to create a post at the same time and creating the same post_id?
The advantage of this system is that it makes post history (and
differencing) very simple. Whenever someone wants to modify a post I'd use
the same post_id as the original and increment the revision_id (which, now
that I'm typing this out, has the same problem).