How does autoincrement work internally in a db?
<p>I understand how to use auto_increment, but how does it work internally? 
Couple questions in different scenarios came to mind: </p>

<ol>
<li><p>For a single node DB, does it use mutex to lock entire table, while incrementing a counter and assigning the value to a row, to prevent race conditions? Wouldn't it be a performance problem when doing this? If not, how does it guarantee a unique id be generated when multiple requests come in at the same time? </p></li>
<li><p>For a DB that is replicated, master-master, or master-slave, it wouldn't make sense to lock up the entire DB table on all the replicas for the auto-increment id, would it?</p></li>
<li><p>For a DB that is distributed (think Cassandra for example), it really isn't possible to have an auto-increment feature, is it? Because the DBs are working independently for fast WRITEs. </p></li>
</ol>

<p>How would you design a distributed system that supports auto_increment?</p>