|
hi all, just wondering if Sybase SQL Anywhere has a feature equivalent to SQL Server 2008's Change Tracking? If not, is the timestamp column type with a DEFAULT TIMESTAMP the recommended approach for application level optimistic concurrency for SQL Anywhere? |
|
I can't tell the real answer (simply since I have not used SQL Server's Change Tracking feature myself), however, to find out what rows have been changed lately (and by whom), it's a very common SA solution to provide each relevant table with a datetime column with DEFAULT TIMESTAMP (and possibly a char column with DEFAULT LAST USER). These columns are automatically updated each time the according rows are inserted or updated. Note, in contrast to MS SQL/Sybase ASE, a DEFAULT TIMESTAMP is a real datetime/timestamp value and no binary "row version counter", so you can query for real date ranges. But it's not inherently used for optimistic conncurrency control - for these types, you can use the according API features or SQL statements with according FOR UPDATE BY VALUES/TIMESTAMP clauses - cf. this doc page on Updatable statements and concurrency control . - And note, these column defaults are defaults, i.e. if you update the row, you are still free to overwrite the values with whatever value you provide: Say, your table MyTable has an according dtLastChanged DEFAULT TIMESTAMP column, you can still do the following: -- How to leave the DEFAULT TIMESTAMP unchanged... UPDATE MyTable SET Col2 = 'Test', dtLastChanged = dtLastChanged WHERE pk = 1; For complete change tracking, note that SQL Anywhere has both
Thanks Volker, the challenge is that we wish to implement offline optimistic concurrency. Our client (UI) requests data, the user changes it, and then tries to save it back to the database. If another user has changed the same records since the client requested it, we wish to reject the change and inform the user that a conflicting change has occurred. To do this, we require a guaranteed unique field as a version marker. Would a timestamp column give us guaranteed uniqueness?
(27 Sep '13, 02:10)
Scott Baldwin
Replies hidden
1
Scott From the documentation: Columns declared with DEFAULT TIMESTAMP contain unique values so that applications can detect near-simultaneous updates to the same row. If the present timestamp value is the same as the last value, it is incremented by the value of the default_timestamp_increment option. HTH
(27 Sep '13, 02:53)
Thomas Dueme...
What do you mean by "offline optimistic concurrency"? How do you handle transactions? I.e. is the "reading data" and "changing data" within the same transaction? Note, independent of the chosen isolation level, writers always block other writers (on the same row), so when two concurrent transactions try to modify the same row, one will be rolled back. That's during parallel transactions, as stated. In case reading and writing are not within the same transaction, the cursor characteristics usually define whether a changed row will be noticed. What API are you using? FWIW, the topic "Lost Updates" is discussed in details in the docs, and there is a bunch of pages on the complex topic "Transactions and isolation levels.
(27 Sep '13, 03:53)
Volker Barth
Hm, that requirement looks rather different to me than the cited SQL Server Change Tracking mechanism - it's just "normal concurrent transaction handling", isn't it?
(27 Sep '13, 04:04)
Volker Barth
1
Yes, a TIMESTAMP column declared with DEFAULT TIMESTAMP can be used to check for conflicting updates after-the-fact. This is optimistic concurrency control where (1) the application retrieves the old timestamp value but does not lock anything, say, when retrieving and displaying a row, (2) lets the user have control to make some changes to the row on-screen, and then (3) when the user says "save" the application uses an UPDATE with a predicate that checks for conflicting updates: WHERE timestamp_column = old_value. If the update affects 1 row then optimism was justified; if the update affects zero rows then the user has to try again (re-retrieve). There are other techniques: WHERE every_updatable_column = old_value, WHERE every_column_that_has_changed = old_value, and so on. The timestamp technique is extreme since it bars two users from simultaneously changing two completely separate and unrelated columns in the same row. The best example is one user performing a long and complex update involving the parent row and many child rows, while another user slides in and out to fix a speling misteak in an inconsequential column in one of those rows... since the first COMMIT wins, the hard-working user has no resort but to pick up his baseball bat and negotiate office etiquette with the other user. Volker wonders what the word "offline" means, as do I, having never seen it together with "optimistic concurrency". Perhaps it means "no locks", as in a long-running transaction that isn't really a transaction in the database sense since no locks are held.
(27 Sep '13, 07:45)
Breck Carter
Thanks Breck and Volker, so to clarify, "Offline optimistic concurrency" is exactly as you described Breck. We are using Sql Anywhere as the back-end for a multi-user, client/server desktop application. We are using the capi to communicate with dbeng12. The server allows multi-threaded access to the database server, meaning multiple users can be concurrently reading and updating the database. We need a mechanism to ensure that we don't have "Lost Updates" in the case where a user has stale data. It appears as though the DEFAULT TIMESTAMP satisfies this, although I do have 1 small concern, and that is what happens if the user changes the system clock? Can we get into a situation where the timestamp is no longer monotonically increasing, or does Sql Anywhere have some tricks up it's sleeve to account for this?
(02 Oct '13, 20:56)
Scott Baldwin
Well, so you are "simply" using a classic OLTP system with multiple users reading and modifyng data in parallel - i.e. they are constantly connected to the same database during their activity? If my understanding is correct, then you should be able to use all common variants of pessimistic and/or optimistic concurrency control. Then I guess for optimistic CC, you could simply use the native SQL support with SELECT statements and the FOR UPDATE BY VALUES/TIMESTAMP clauses, as stated in my answer.
Is there any particular reason to use that quite low-level API over classic APIs like ESQL, ODBC or the like? (I'd thought it is primarily used to support database access for other languages like PHP, Perl and the like, and possibly for native access on the OS X platform...)
(03 Oct '13, 09:06)
Volker Barth
Our business logic is written in C++ as it needs to run on both Mac OS X and Windows.
(04 Oct '13, 03:04)
Scott Baldwin
Ah, I see. As to the timestamp problem: I'm quite sure that you cannot expect timestamp values to increase monotonically when the machine's system clock is set back. I'm not sure whether a running database engine would make sure that these values can only increase even if the system clock would be set back (and for a portable device, I would even think it should follow timezones accordingly, so a time decrease would be expected...), but if the database is shutdown and restarted on a system with a chronologically earlier system time, the newly generated timestamp values will be earlier, too, AFAIK. But that should not be a problem here: In my understanding, if you are about to make sure that between reading a row and doing the modification, the row has not been modified by another transaction, you would simply have to make sure the timestamp values (say, of a column "dtLastChanged") are identical between the originally fetched state and the current state... That is, you would to check for identical values, not for increased ones, and therefore a re-set system time should not lead to troubles (except for the extremely rare case that a logically "later" modification would be done at exactly that nanosecond the previous modification was made...) As cited above, I'd expect that the builtin optimistic CC facilies via FOR UPDATE BY TIMESTAMP will handle such situations automatically, i.e. without the need for an explicitly added DEFAULT TIMESTAMP column.
(04 Oct '13, 03:56)
Volker Barth
Just to add: As the SQL Anywhere C API is built on top of Embedded SQL, I would assume the following quote from the doc topic "Lost Updates" would apply to the C API, as well:
Note, that's just my understanding, and I'm still not aware how you are actually using transactions and isolation levels in your application...
(04 Oct '13, 04:13)
Volker Barth
Optimistic concurrency control does not rely on locks at all. It allows for long periods of time where the end user works on a copy of data in the application, with the optimistic assumption that no changes will be made to the underlying data in the database by anyone else until this person is done. When it comes time to update, the assumption is checked by including the timestamp column in the WHERE clause. If the timestamp column still has the old value retrieved by the application then the optimism was justified and the update proceeds; if not, the row is not updated, and the user is punished for the unjustified optimism by having to repeat the process. Locks are only in effect between update and commit, not between select and update. If an application select or cursor is coded to get locks, and the user is given control to do work, that's a long-running transaction which is generally regarded as evil. Optimistic concurrency control is a way of guaranteeing short-running database transactions while allowing the user long periods of work time.
(04 Oct '13, 08:56)
Breck Carter
Yes, that's sure but did I imply the opposite? (I'm asking as you have replied to my comment that should point to the "SELECT FOR UPDATE BY VALUES | TIMESTAMP" OCC means...)
(04 Oct '13, 09:09)
Volker Barth
1
Thanks Volker and Breck. Just to clarify the way we are using SQL Anywhere. Firstly, our end user client application does NOT talk directly to Sql Anywhere, which from what I understand negates the option of using updateable cursors in the client. We have a service oriented application "Server" that our client talks directly to for the purposes of extracting application data, and making changes to that data. From Sql Anywhere's point of view, our "Server" is its direct client. This is a classic 3-tier architecture,
, and any of these layers could be hosted on separate network connected machines. The expected flow of data is UI calls service layer requesting data to present to the user. Service layer opens read only result sets against the persistence layer (SQL Anywhere) to fulfill the UI's request, populates internal data structures, and then returns them across the network boundary to the UI, closing the corresponding result sets from Sql Anywhere (this is important to ensure statelessness of the server). The user then makes decisions based on the presented data, and may decide to change it. The UI then calls the service layer requesting a change to the data. The Service Layer then forms the necessary UPDATE, INSERT and DELETE SQL statements to persist these changes int Sql Anywhere. It is the possibility of Lost Updates from the UI perspective that we are trying to guard against here, and the UI is NOT the client of Sql Anywhere. We are planning to use Snapshot isolation in the database so that our server can be multi-threaded, and that readers will not block writers, but we still require concurrency control. Some of our operations, we explicitly implement a pessimistic locking scheme because the cost of conflicts to the user is so high that it is better to not let them change data in the first place, however, for most of the application, we want to employ optimistic concurrency control because the probability of conflict is low, and the consequence are manageable.
(06 Oct '13, 19:57)
Scott Baldwin
@Scott, thanks for the detailed clarification. Then I guess the timestamp-based approach you are planning (i.e. comparing the originally fetched timestamp with the timestamp just before (or during) the update is done) is the appropriate way to go. In case the application may keep running continuously even when the database engine is restarted, do you plan to additionally check for decreased timestamp values (i.e. say, in order to raise a notification that "database-time" has been reset)?
(07 Oct '13, 08:31)
Volker Barth
@Volker, actually, because of the issues with timestamp being clock dependent, we are now actually leaning towards not using it, and instead having a globally (from the database perspective) monotonically increasing BIGINT value as a versioning field. This would be achieved by using a single table (say Transaction table) with a version field of UNSIGNED BIGINT AUTOINCREMENT that we would insert a row into at the beginning of a request (wrapped in its own DB Transaction, and not part of the transaction we use to perform the rest of the work), and then use this version for the rest of the work that needs to be done. So any row that is inserted, updated, or soft deleted (setting a deleted field to true) as part of a service call will then inherit the version number assigned to that individual service request. I realize there is a small concurrency issue with this because of the way locks are acquired on an insert, but it's something we are willing to trial. I think the clock issues would have been minor so long as we weren't trying to infer any kind of order, but there were some concerns raised around the possibility (although remote) of it being non-unique, especially in cases where the server running the database engine is being synchronized with a time server.
(07 Oct '13, 17:38)
Scott Baldwin
Though I would suspect that this should really be extremely rare: As long as timestamps are used as DEFAULT TIMESTAMP, they are said to be unique, as Thomas has cited. (As stated, I do not know how a running database engine will handle system-clock resets/syncs to guarantee that uniqueness - that would surely be a good question on its own...)
(08 Oct '13, 03:13)
Volker Barth
1
If you don't like the timestamp solution, just store a local "update_count" column in each row and increment it with each update. Code the query as SELECT t.update_count, ... INTO @old_update_count, ... FROM t ... Use UPDATE SET t.update_count = @old_update_count + 1, ... WHERE t.update_count = @old_update_count ... as your optimistic concurrency check; TRUE means OK to update, FALSE means some other connection updated the row after this connection queried it. ...no need for some single-threaded process to calculate the versioning value, this isn't a primary key being calculated, it's just a steenking column update counter :)
(08 Oct '13, 07:54)
Breck Carter
|