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 ?

asked 24 Aug '12, 08:29

Serge's gravatar image

Serge
1266715
accept rate: 0%


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.

permanent link

answered 24 Aug '12, 08:44

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 24 Aug '12, 09:09

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 24 Aug '12, 08:29

question was seen: 596 times

last updated: 24 Aug '12, 09:09