Re: Concurrency control in web applications

Sybase and MS-SQL have a datatype called "timestamp" (which isn't time related at all) that can be used for this. It is an 8 byte binary column that gets updated each time the row is updated, so you can easily check if the data that the user has been editing was changed when applying the update. This is essentially the same as keeping track of changes with a counter, but happens automatically (and the timestamp column can't be modified manually).

The alternative is as others have suggested to implement your own locking mechanism via a secondary table. Be sure to include a time when the lock was acquired so that you can force the release of stale locks.

Michael

Comment on Re: Concurrency control in web applications

Replies are listed 'Best First'.
Re: Re: Concurrency control in web applications by liz (Monsignor) on Oct 24, 2003 at 21:47 UTC
MySQL also has such a field type, but only the first timestamp field in a record that is not updated or has the value NULL assigned, will actually be automatically set. And you can assign it also. It seems however that this feature is subject to change, so you might want to check out to make sure. From the MySQL documentation: The TIMESTAMP column type has varying properties and behaviour, depending on the MySQL version and the SQL mode the server is running in. TIMESTAMP behaviour when running in SAPDB mode When MySQL is running in SAPDB mode, TIMESTAMP behaves like DATETIME. No automatic updating of TIMESTAMP columns occurs, as described in the following paragraphs. MySQL can be run in SAPDB mode as of version 4.1.1. See section 4.1.1 mysqld Command-line Options. TIMESTAMP behaviour when not running in SAPDB mode The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Liz	[reply]
Re^2: Concurrency control in web applications by adrianh (Chancellor) on Oct 24, 2003 at 21:58 UTC
In addition to the points liz mentioned timestamp columns only have a one second resolution - so if you do a read/update in a smaller interval you're out of luck. Personally I'd just use a counter.	[reply]