This is a side-question to this thread...but has anybody every done row-level locking for edit?
I can see it: create table t(id int primary key,stuff text, lock l); insert into t values(1,'stuff1',0); select * from t where id=1 and lock=0; // or drop lock to get all and check lock!=0 to make "row is locked" message 1|stuff1|0 update t set lock=1 where id=1 and lock=0; // lock value would actually be the process id of your program, or in the case of multiple-machines add the machine name plus process id to guarantee uniqueness. select * from t where id=1 and lock=0; (no results -- so 2nd query returns nothing or drop lock=0 from query and check results to make "row is locked" message update t set stuff='stuff1a',lock=0 where id=1 and lock=1; Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Roger Binns [rog...@rogerbinns.com] Sent: Sunday, July 31, 2011 10:13 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Will SQLite supports UnQL? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Any database system can be nonsensical. If you have multiple people editing prices for the same item even with a single instance database you can have the wrong final answer. But that is a procedural and business issue, not a technological one. Using a less highly charged example, how about a photos database? Using multi-master it is possible on one instance for someone to edit a photo's description while someone else on a different instance sets the location. Your conflict resolution can simply merge both changes as the fields are independent of each other. On the other hand someone setting the longitude on one system and latitude on the other is likely a genuine conflict that cannot be automatically resolved since the fields are dependent on each other. If you also wanted a view count then you would not have a single field labelled "view count" since there is no way to have a sane value with multi-master. Instead what you do is record the views themselves. This could be tracking every view in a log, or each server instance could use a key derived from its name. When you want to display the actual view count the value would have to be calculated from the pieces, and map/reduce is frequently used to do this. These kinds of databases are atomic but not transactional. Instead of recording final values as you do with transactional SQL, you instead record the transaction events themselves and calculate the final values from those. http://guide.couchdb.org/draft/recipes.html#banking http://stackoverflow.com/questions/299723/can-i-do-transactions-and-locks-in-couchdb Going back to the prices issue in this thread, the final price is made up of multiple parts (component prices, markup, taxes, profit margins, shipping and handling, discounting, competitive pressures etc). Those can be recorded and edited separately in a multi-master environment without conflicts. You do not have to use the "NoSQL" databases for everything and even the authors of the systems would tell you to use the regular databases we have decades of experience and best practises with for financial transactions. But there are also places where they are significantly more productive. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk41cQ8ACgkQmOOfHg372QTQuQCg4mmFFen351KbJH4elTU5NdTj uv0An2bJhP7GgExdp25uAesxiSbpEKsI =Co8y -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
