I have a question regarding database transactions where I'm not sure if my understanding is correct. I want to implement a user login in a java application. As there might be multiple servers acting on the same database, and each user can only log in one of those servers, the database has to store a flag if a user is already logged in.

So assume following table structure:

```
ID | User | PWD | Server

```

where `Server` is the reference to the server where the user is logged in to, or `NULL` if he's not logged in.

When a user logs in, I have to first check if the server value is NULL, and if not, set it to the correspondig reference value. But what happens when in between these 2 statements the user logs in on another server? Can such a condition be detected by transactions? e.g. (pseudo)

```
conn.setAutoCommit(false);
ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM users WHERE user = 'usr' AND password = 'pwd'");
if (rs.next()) {
  rs.getInt("Server");
  if (rs.wasNull()) {
    // Not logged in
    conn.createStatement().execute("UPDATE users SET server = 123 WHERE user = 'usr' AND password = 'pwd'");
    conn.commit();
  }
} else {
  // Usr/Pwd wrong
}
conn.rollback();

```

What happens if the user logs in between the select and update statements on another server? does the commit fail as the table was altered by someone else during the transaction, or is it executed nontheless?

Should I better use an approach like

```
UPDATE users SET server = 123 WHERE user = 'usr' AND pwd = 'pwd' AND server IS NULL

```

and check if 0 rows were affected? If so I have to check if the user exists in a query.

Simmilar issues arise when registering a new user with INSERT. Shoud I use the transaction approach, or declare the user column as UNIQUE and catch SQL exceptions?