I have been reading about database isolation level and TransactionScope here, here and here, but none seems to answer my question. The problem I have is a simple read and write problem.
A concrete scenario is describe below
Prcess1 and process2 does not communicate with each other and I am hoping for a database level solution to keep it that way. I know that Isolation level Serializable solves the problem because the read lock acquired in step2 prevents step3 from succeeding.
In order to find a less restrictive isolation level, I've also read up on ReadCommitted and row versioning. According to this text from here
Locking and row versioning prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time. Without locking or row versioning, queries executed against that data could produce unexpected results by returning data that has not yet been committed in the database
It seems to imply that row versioning could be a solution for the read and write problem. In step4, with row versioning, database should be able to detect that it is trying to change a row whose version has already been changed since the read in step2. But my experiment proved that this is not the behavior. With ReadCommited isolation and READ_COMMITTED_SNAPSHO of the database set to ON, step4 succeeded with the state updated to Shipped.
My question is, besides isolation level Serializable, is there another database level solution to the read and write problem describe above?
What row versioning in SQL server does is ( this is equivalent to how Multi version concurrency control MVCC in other databases work too ) - for each changed row it maintains a separate version so that if there is a read request for that row - it utilizes that version instead of referring to yet uncommitted row. This is a better way to implement concurrency as reads then then do not require lock and so is implemented in all major databases. Now you can see why using row versioning ( with either statement based or transaction level read consistency) only gives you guarantee of consistent read ( using the version of data before the change started happening by yet uncommitted transaction).
If you are looking for a solution from purely database side I think Serializable isolation level is your best best. Assuming not many transactions will be working on the same data row concurrently your time to hold lock can be minimal.
Another solution will be to use optimistic concurrency control using a version column in the table. The later transaction will have "where version =1" in the update clause which will return 0 rows updated since the version has already been increased to 2 by the first transaction. This can be treated as a logical error on the application side and message propagated accordingly.