According to the source code (present in git v2.17), if a commit lead to an already existing sha1, this is what would happen on Linux (for other operating systems it might be different).
Would the commit succeed?
Yes and no: the git commit command would return as if in success, but the new commit object would not be created.
Could it later be checked out as a detached head?
No.
Reference :
file sha1-file.c (commit fc1395f4a491a7da46a446233531005634eb979d)
int finalize_object_file(const char *tmpfile, const char *filename)
{
    int ret = 0;
    if (object_creation_mode == OBJECT_CREATION_USES_RENAMES)
        goto try_rename;
    else if (link(tmpfile, filename))
        ret = errno;
    /*
     * Coda hack - coda doesn't like cross-directory links,
     * ...
     */
    if (ret && ret != EEXIST) {
    try_rename:
        if (!rename(tmpfile, filename))
            goto out;
        ret = errno;
    }
    unlink_or_warn(tmpfile);
    if (ret) {
        if (ret != EEXIST) {
            return error_errno("unable to write sha1 filename %s", filename);
        }
        /* FIXME!!! Collision check here ? */
    }
out:
    if (adjust_shared_perm(filename))
        return error("unable to set permission to '%s'", filename);
    return 0;
}
The link fails with EEXIST, the temporary file is removed, and the code continues until the return 0 (through the FIXME, and the adjust_shared_perm() which has no reason to fail).