4 ms·

There appears to be some confusion in the terminology here - database transactions are not meant to prevent the "user A and B are editing object X at the same

There appears to be some confusion in the terminology here - database transactions are not meant to prevent the "user A and B are editing object X at the same time". - Database transactions ensure that when you ask the database to modify X, it either happens in its entirety or it doesn't. It's not meant to prevent concurrent edits, it's meant to keep the data in the database consistent. - CRDT's allow user A and B to edit object X concurrently, because it's mathematically proven that two instances of the CRDT can be merged later without conflict. You also don't need to start locking things yourself if all you want is to prevent user A from overwriting user B's changes. Add a column "version" to your table and when the user submits a change, include this version number in the change request. Then upon database UPDATE, check if the version in the DB still matches expectations. If it doesn't you reject the UPDATE and you can report back to the user what happened. You could easily apply this logic using triggers which will ensure that no matter the source of the UPDATE statements, it will be a certainty older versions can't overwrite newer versions.

lmm 6y ago
> - Database transactions ensure that when you ask the database to modify X, it either happens in its entirety or it doesn't. It's not meant to prevent concurrent edits, it's meant to keep the data in the database consistent. It keeps the database consistent. But fundamentally that's only worth something if you can use it to make things consistent for the user. > - CRDT's allow user A and B to edit object X concurrently, because it's mathematically proven that two instances of the CRDT can be merged later without conflict. Right. But the way that works in practice is that your datastore needs to store both user A's edit and user B's edit and then handle that convergence, and if you use a datastore that's actually designed for distributed use like Riak or Cassandra then it will give you the tools for doing that. An RDBMS simply can't do that because it's built around maintaining the illusion that there's a single version of each row (even when it's actually using MVCC behind the scenes). Yes, you can build your own CRDTs "in userspace" but you have to do things like use two tables to represent a 2P-set (two columns would be bad enough, but no, SQL databases don't have collection column types so you really do have to create two extra tables for each set property you have), at which point the database won't be giving you any help. > Add a column "version" to your table and when the user submits a change, include this version number in the change request. Then upon database UPDATE, check if the version in the DB still matches expectations. If it doesn't you reject the UPDATE and you can report back to the user what happened. You could easily apply this logic using triggers which will ensure that no matter the source of the UPDATE statements, it will be a certainty older versions can't overwrite newer versions. That's hacky and manual - you're effectively duplicating something that the database itself is doing at a lower-level, so you could do it much more efficiently with a datastore that would just expose that to you. And it still doesn't really give the behaviour you want - you don't want to refuse to store the incompatible change, you want to save both versions and somehow merge them in the future (possibly by asking the user to do so); an approach that destroys data by default is not ideal. Yes, databases are Turing-complete and you can build whatever you want in them, but if you don't go with the grain of what the tools and built-in features expect then you're giving up all the advantages of using that kind of database in the first place. You're better off picking a datastore that has first-class support for being used in the kind of way you want to use it.

lmm 6y ago

> - Database transactions ensure that when you ask the database to modify X, it either happens in its entirety or it doesn't. It's not meant to prevent concurrent edits, it's meant to keep the data in the database consistent. It keeps the database consistent. But fundamentally that's only worth something if you can use it to make things consistent for the user. > - CRDT's allow user A and B to edit object X concurrently, because it's mathematically proven that two instances of the CRDT can be merged later without conflict. Right. But the way that works in practice is that your datastore needs to store both user A's edit and user B's edit and then handle that convergence, and if you use a datastore that's actually designed for distributed use like Riak or Cassandra then it will give you the tools for doing that. An RDBMS simply can't do that because it's built around maintaining the illusion that there's a single version of each row (even when it's actually using MVCC behind the scenes). Yes, you can build your own CRDTs "in userspace" but you have to do things like use two tables to represent a 2P-set (two columns would be bad enough, but no, SQL databases don't have collection column types so you really do have to create two extra tables for each set property you have), at which point the database won't be giving you any help. > Add a column "version" to your table and when the user submits a change, include this version number in the change request. Then upon database UPDATE, check if the version in the DB still matches expectations. If it doesn't you reject the UPDATE and you can report back to the user what happened. You could easily apply this logic using triggers which will ensure that no matter the source of the UPDATE statements, it will be a certainty older versions can't overwrite newer versions. That's hacky and manual - you're effectively duplicating something that the database itself is doing at a lower-level, so you could do it much more efficiently with a datastore that would just expose that to you. And it still doesn't really give the behaviour you want - you don't want to refuse to store the incompatible change, you want to save both versions and somehow merge them in the future (possibly by asking the user to do so); an approach that destroys data by default is not ideal. Yes, databases are Turing-complete and you can build whatever you want in them, but if you don't go with the grain of what the tools and built-in features expect then you're giving up all the advantages of using that kind of database in the first place. You're better off picking a datastore that has first-class support for being used in the kind of way you want to use it.