Long time ago I used an schema like the following to solve a problem like this. We had a system that was able to work either with SQL tables or with VFP tables. Our key generation code looked like:

When talking to VFP tables.

BEGIN TRANSACTION
 UPDATE key_ SET lastkey = lastkey + 1 WHERE pk_key = 'employee'
 SELECT lastkey FROM key_ WHERE pk_key = 'employee' INTO CURSOR NewKey
END TRANSACION

When talking to SQL tables.

SQLEXEC( n, "BEGIN TRANSACTION" )
SQLEXEC( n, "UPDATE key_ SET lastkey = lastkey + 1 WHERE pk_key = 'employee'" )
SQLEXEC( n, "SELECT lastkey FROM key_ WHERE pk_key = 'employee'", 'NewKey' )
SQLEXEC( n, 'COMMIT')

As far as I remember it was very important to have the UPDATE command as the first command in the transaction, that way, as soon as it gets executed the table is locked. As you can see, the table is only locked a few miliseconds.

If two people execute this code at the exact same time, then one of them will lock the table, generate a new key (UPDATE), get the key value (SELECT) and unlock the table. Meanwhile, the second user will experience a minimal delay waiting for the first user to unlock the table. As soon as the first user unlocks the table, the second user follows the same procedure: lock the table, generate a new key (UPDATE), get the key value (SELECT) and unlock the table.