Update query race condition
You will typically get result #1 (Value=2).

Update locks make the queries run sequentially

The UPDATE query will place an update lock from the start, because it knows that it will update the row. This prevents another query from placing an update lock of its own, meaning that they will run sequentially.

There are different so-called isolation levels that may be implemented slightly differently in different database platforms, but to the best of my knowledge, the above reflects how it works in SQL Server.

The second scenario

Your second scenario (read-only lock, which escalates to an exclusive read-write lock) can only happen if you separate the UPDATE into two statements, and provided you have a less restrictive isolation level (i.e. not for instance SERIALIZABLE).

DECLARE @val int;

BEGIN TRANSACTION;

    SELECT @val=Value FROM [Some table] WHERE Id=1;
    UPDATE [Some table] SET Value=@val+1 WHERE Id=1;

COMMIT TRANSACTION;


Again, if you have a restrictive isolation like SERIALIZABLE, the initial SELECT statement will place an exclusive lock on the row immediately, preventing other processes from viewing or changing it. If you use, for instance, READ COMMITTED (the default level), the SELECT statement places a shared lock, so other processes can read the row as well.

In-memory OLTP (Hekaton)

If you're using in-memory tables, like @Piotr commented, the second query will fail because it doesn't wait for the first query to complete. Because of this, the row would have changed by the time the second query tries to commit its own changes, so the result will be Value=1.

For this reason, it is best practice to trap and retry data modifications in the client app when using in-memory tables.