How works snapshot isolation in case competitive update?

[former_SQLA_member1694928]

I would like to have further explantion for case when snapshot isolation is settled and 2 connections modified the same row (id=111, col1 = 0,col2 = 0):

1st connect UPDATE myTable SET col1 = 1 WHERE id=111;

2nd connect UPDATE myTable SET col2 = 2 WHERE id=111;

2nd connect COMMIT;

1st connect COMMIT;

Will be these row id=111, col1 = 0,col2 = 2 or id=111, col1 = 1,col2 = 0 or id=111, col1 = 1,col2 = 2 ?

[VolkerBarth · Accepted solution]

According to the docs, in SQL Anywhere writers always block writers, even with snapshot isolation, so I woud conclude that the 2nd UPDATE statement would already be blocked, as the first UPDATE is not yet commited. So in the end, the update from the 1st connection would be permanent, i.e. with values id=111, col1 = 1, col2 = 0.

Note: I assume both transactions already have begun before the 1st UPDATE happens.

To cite:

With snapshot isolation, an update conflict can occur when a transaction sees an old version of a row and tries to update or delete it. When this happens, the server gives an error when it detects the conflict. For a committed change, this is when the update or delete is attempted. For an uncommitted change, the update or delete blocks and the server returns the error when the change commits.

Glenn has blogged about that here as well - please have a look at the blog comments in particular.