Informative article.

But I am still trying to understand why the transaction isolation level plays a role here. Before a connection is established, there is no isolation level to that "would-be" connection. The only thing I can think of is that the user table used in the trigger have a table-scope lock (by either another existing connection or some other applications) that prevents the table being accessed when the trigger trying to update the user table when a new connection is being established. So this may not have anything to do with the isolation level, but because of something else, like lock escalation, or simply a table-wide lock on the hot table.