Re: A bit OT: handling multiple users in DB
Am 23.09.2008 um 01:20 schrieb Kurt Kaufman: Bob Sneidar wrote: ...Sorry for the lengthy post. Not at all! This is a fascinating discussion. Yes, very interesting and appreciated, Bob! I was under the false impression that the automatic record-locking mechanisms would handle everything for me. I also had this naive thinking :-) Thanks for setting me straight! Looks like I have a lot more to learn before I dive into db design. -Kurt Regards Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Klaus, FWIW, here is the information SDB maintains internally to handle multiuser access: A list of DBs currently open by the user or client: local clientDbList -- array by clientId:dbId1, dbId2, etc A list of the current record position and access privileges for each user/client currently accessing any open DBs local dbClientList -- array by dbId:client id,writeAccessType,current position The main indexes of every DB that has been opened since the user or the server app started local indexList -- array by dbId:cardIndex A list of locked records by DB local lockedRecordList -- array by dbId: record id, client id Path and generic locking options to each open DB local sdbDbPathList -- array by dbId: db stack path, autoLockOn, readLocksEnforced Information to support record translation if client and server are running on different platforms local sdbTranslationList -- array by clientId: 1=ASCIIANSI, 0=None, or -1=ANSIASCII + tab ipcProtocol [tp,ae,pc,dc,sf] Rob Cozens I must be the change I want to see in the world. -- Gandhi ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Rob, Hi Klaus, FWIW, here is the information SDB maintains internally to handle multiuser access: A list of DBs currently open by the user or client: local clientDbList -- array by clientId:dbId1, dbId2, etc A list of the current record position and access privileges for each user/client currently accessing any open DBs local dbClientList -- array by dbId:client id,writeAccessType,current position The main indexes of every DB that has been opened since the user or the server app started local indexList -- array by dbId:cardIndex A list of locked records by DB local lockedRecordList -- array by dbId: record id, client id Path and generic locking options to each open DB local sdbDbPathList -- array by dbId: db stack path, autoLockOn, readLocksEnforced Information to support record translation if client and server are running on different platforms local sdbTranslationList -- array by clientId: 1=ASCIIANSI, 0=None, or -1=ANSIASCII + tab ipcProtocol [tp,ae,pc,dc,sf] Thanks, but unfortunately our users demand a SQL database. Rob Cozens I must be the change I want to see in the world. -- Gandhi Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Klaus, I would expect all that multiuser functionality to be built into whatever implementation of SQL you employ; but relational DBs are not my strong suit. If, however, you're trying to allow multiple users to access a single user [ie. non-client/server] SQL DB, you will probably need to maintain arrays similar to SDB's. Rob Cozens CCW Serendipity Software Company And I, which was two fooles, do so grow three; Who are a little wise, the best fooles bee. from The Triple Foole by John Donne (1572-1631) ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Rob, Hi Klaus, I would expect all that multiuser functionality to be built into whatever implementation of SQL you employ; it IS, but that's not my problem. but relational DBs are not my strong suit. Same here ;-) If, however, you're trying to allow multiple users to access a single user [ie. non-client/server] SQL DB, you will probably need to maintain arrays similar to SDB's. Rob Cozens CCW Serendipity Software Company And I, which was two fooles, do so grow three; Who are a little wise, the best fooles bee. from The Triple Foole by John Donne (1572-1631) Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
A bit OT: handling multiple users in DB
Hi all, this is a bit off topic, but maybe someone can give me some helpful hints. Is someone of you working with databases with multiple users? If yes, how do you handle concurrent transactions? I mean how do you solve the possible overwriting of data when both users work with cursors and are allowed to update data and how do you update a cursor (on the fly?) when its data may have been updated? Know what I mean? I searched the net and found some hints, but maybe you have some really clever solutions that you are willing to share :-) Thanks in advance! Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
On Mon, Sep 22, 2008 at 7:46 PM, Klaus Major [EMAIL PROTECTED] wrote: Hi all, this is a bit off topic, but maybe someone can give me some helpful hints. Is someone of you working with databases with multiple users? If yes, how do you handle concurrent transactions? I mean how do you solve the possible overwriting of data when both users work with cursors and are allowed to update data and how do you update a cursor (on the fly?) when its data may have been updated? I did one project like this and I solved it by having a table that recorded when a user was accessing a record. Another person could look at that record, but would get a warning that the record was already being used, and the Save button was disabled. The only problem was if the first user didn't log off or crashed, so I implemented a time-out feature for that. Cheers, Sarah ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
MySQL will do what you want. It's a black box to store an manupulate data. Most of the time the transactions are so fast, it doesn't matter. In the case of mySQL, it's multi user by design and if one is really worried about it, there's COMMIT and ROLLBACK. SQL Lite is not multi-user. Hi all, this is a bit off topic, but maybe someone can give me some helpful hints. Is someone of you working with databases with multiple users? If yes, how do you handle concurrent transactions? I mean how do you solve the possible overwriting of data when both users work with cursors and are allowed to update data and how do you update a cursor (on the fly?) when its data may have been updated? Know what I mean? I searched the net and found some hints, but maybe you have some really clever solutions that you are willing to share :-) Thanks in advance! Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de -- stephen barncard s a n f r a n c i s c o - - - - - - - - - - - - ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Sarah, On Mon, Sep 22, 2008 at 7:46 PM, Klaus Major [EMAIL PROTECTED] wrote: Hi all, this is a bit off topic, but maybe someone can give me some helpful hints. Is someone of you working with databases with multiple users? If yes, how do you handle concurrent transactions? I mean how do you solve the possible overwriting of data when both users work with cursors and are allowed to update data and how do you update a cursor (on the fly?) when its data may have been updated? I did one project like this and I solved it by having a table that recorded when a user was accessing a record. Another person could look at that record, but would get a warning that the record was already being used, and the Save button was disabled. The only problem was if the first user didn't log off or crashed, so I implemented a time-out feature for that. Thanks for this info! I was afraid that one would have to do this manually the hard way :-) Cheers, Sarah Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hiya, If you are using SQLite, check Item 5 in: www.sqlite.org/faq.html Cheers, Luis. On 22 Sep 2008, at 10:46, Klaus Major wrote: Hi all, this is a bit off topic, but maybe someone can give me some helpful hints. Is someone of you working with databases with multiple users? If yes, how do you handle concurrent transactions? I mean how do you solve the possible overwriting of data when both users work with cursors and are allowed to update data and how do you update a cursor (on the fly?) when its data may have been updated? Know what I mean? I searched the net and found some hints, but maybe you have some really clever solutions that you are willing to share :-) Thanks in advance! Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Stephen, looks like my questions were not clear enough! MySQL will do what you want. Not really ;-) It's a black box to store an manupulate data. Most of the time the transactions are so fast, it doesn't matter. Not really, see below ;-) Scenario: 2 users (A and B) are browsing through data in a (local) db cursor. These data are already old/not up to date once they appear on the target machine, since another user (C) may have updated/deleted or altered the data a millisecond after users A and B receive their data/cursor. Now when both users decide to change (update) data, who hits the update button first is the loser, since the data of the other user will overwrite his data! Not to mention the fact that the (local) cursor of one user does not reflect the changes that the other user already made etc. Know what I mean? Therefore one needs to implement a nifty solution to avoid exactly this situation. In the case of mySQL, it's multi user by design and if one is really worried about it, there's COMMIT and ROLLBACK. SQL Lite is not multi-user. -- stephen barncard s a n f r a n c i s c o Regards Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Luis, Hiya, If you are using SQLite, check Item 5 in: www.sqlite.org/faq.html Thanks, I should have mentioned that I mean MySQL and all other multiuser databases. Cheers, Luis. Regards Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hiya, I suppose you could have a column for each, and each entry tagged with the user ID and time. If both values need to be shown you could display the data for both on a table showing the change and the time it was committed. Cheers, Luis. On 22 Sep 2008, at 15:22, Klaus Major wrote: Hi Luis, Hiya, If you are using SQLite, check Item 5 in: www.sqlite.org/faq.html Thanks, I should have mentioned that I mean MySQL and all other multiuser databases. Cheers, Luis. Regards Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Luis, Hiya, I suppose you could have a column for each, and each entry tagged with the user ID and time. If both values need to be shown you could display the data for both on a table showing the change and the time it was committed. Yes, I found a similar solution on the net, but I was strongly hoping there is a much more clever and obvious solution that I was just overlooking ;-) Cheers, Luis. Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hiya, It depends on what your concern is: Do you want to allow them both write access? If they both have the ability to write, then the last to write will be the one visible - This is the standard behaviour. If you want to ensure only one is able to update a field then you lock down their access. If you plan on the need for a rollback, for whatever reason, then you might as well implement a tracking option to list all the changes (I do this for accountability, not for data restore). Cheers, Luis. On 22 Sep 2008, at 15:36, Klaus Major wrote: Hi Luis, Hiya, I suppose you could have a column for each, and each entry tagged with the user ID and time. If both values need to be shown you could display the data for both on a table showing the change and the time it was committed. Yes, I found a similar solution on the net, but I was strongly hoping there is a much more clever and obvious solution that I was just overlooking ;-) Cheers, Luis. Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Luis, Hiya, It depends on what your concern is: Do you want to allow them both write access? Yep. If they both have the ability to write, then the last to write will be the one visible - This is the standard behaviour. If you want to ensure only one is able to update a field then you lock down their access. If you plan on the need for a rollback, for whatever reason, then you might as well implement a tracking option to list all the changes (I do this for accountability, not for data restore). Sure, I know this, I was just hoping for an easier solution :-) But there obviously isn't one :-/ Cheers, Luis. Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
One way is to put a dateTimeLastModified column in every table, and make it a timestamp data type so it automatically gets updated when the record does. Then, when you query data, make sure you get that value and save it locally. Then, when the user goes to Save, check the local and remote timestamps. If , then warn the user, Someone has recently updated this record. After that, what happens depend on your application, either Overwrite or Cancel, or perhaps Merge data? Or perhaps you show both records side by side and the user can decide? On Sep 22, 2008, at 2:46 AM, Klaus Major wrote: Hi all, this is a bit off topic, but maybe someone can give me some helpful hints. Is someone of you working with databases with multiple users? If yes, how do you handle concurrent transactions? I mean how do you solve the possible overwriting of data when both users work with cursors and are allowed to update data and how do you update a cursor (on the fly?) when its data may have been updated? Know what I mean? I searched the net and found some hints, but maybe you have some really clever solutions that you are willing to share :-) Thanks in advance! Best Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Josh, One way is to put a dateTimeLastModified column in every table, and make it a timestamp data type so it automatically gets updated when the record does. Then, when you query data, make sure you get that value and save it locally. Then, when the user goes to Save, check the local and remote timestamps. If , then warn the user, Someone has recently updated this record. Thanks, I was considering something like this. After that, what happens depend on your application, either Overwrite or Cancel, or perhaps Merge data? Or perhaps you show both records side by side and the user can decide? Ah, yes, very good idea! Will surely provide a good user experience, thanks a lot! Regards Klaus Major [EMAIL PROTECTED] http://www.major-k.de ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Klaus,
if you just want to track changes, like, hey, my cursor is outdated,
need to fetch again!, use a table for revisions, this table has at
least three fields, user who made the change, SQL used for the change,
hash or unique id for the revision. When anyone is trying to write,
read, or navigate a previous cursor, check their revision against the
last one in the database. If they differ, then there's something out
dated.
If you wanted to be really neat with this, use an extra field for
storing the SQL command that undo the change just made, so if you're
creating a record like
user_id: 111
revision_hash: some-big-integer
SQL_command: insert into favoriteColors(user_id, color) values ('111','blue');
You also store something like:
SQL_undo_command: remove from favoriteColors where user_id='111' and
color='blue';
Or better, you pick the id of the record created by the SQL command
and use a remove from with the specific id, this way, you'll be able
to rollback to any place you want in time or see a detailed picture of
what changed since your last revision. For example if you use a
revision id as an auto increment integer, and your cursor is like
revision 1 and your software noticed that all of a sudden you're on
revision 5, you know that there were four SQL commands in between, you
can inspect the revision table to see what changed, roll them back or
simply refetch the cursor.
It's easier than it appears...
andre
On Mon, Sep 22, 2008 at 11:52 AM, Klaus Major [EMAIL PROTECTED] wrote:
Hi Luis,
Hiya,
It depends on what your concern is: Do you want to allow them both write
access?
Yep.
If they both have the ability to write, then the last to write will be the
one visible - This is the standard behaviour.
If you want to ensure only one is able to update a field then you lock
down their access.
If you plan on the need for a rollback, for whatever reason, then you
might as well implement a tracking option to list all the changes (I do this
for accountability, not for data restore).
Sure, I know this, I was just hoping for an easier solution :-)
But there obviously isn't one :-/
Cheers,
Luis.
Best
Klaus Major
[EMAIL PROTECTED]
http://www.major-k.de
___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
--
http://www.andregarzia.com All We Do Is Code.
___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
Hi Andre,
Klaus,
if you just want to track changes, like, hey, my cursor is outdated,
need to fetch again!, use a table for revisions, this table has at
least three fields, user who made the change, SQL used for the change,
hash or unique id for the revision. When anyone is trying to write,
read, or navigate a previous cursor, check their revision against the
last one in the database. If they differ, then there's something out
dated.
If you wanted to be really neat with this, use an extra field for
storing the SQL command that undo the change just made, so if you're
creating a record like
user_id: 111
revision_hash: some-big-integer
SQL_command: insert into favoriteColors(user_id, color) values
('111','blue');
You also store something like:
SQL_undo_command: remove from favoriteColors where user_id='111' and
color='blue';
Or better, you pick the id of the record created by the SQL command
and use a remove from with the specific id, this way, you'll be able
to rollback to any place you want in time or see a detailed picture of
what changed since your last revision. For example if you use a
revision id as an auto increment integer, and your cursor is like
revision 1 and your software noticed that all of a sudden you're on
revision 5, you know that there were four SQL commands in between, you
can inspect the revision table to see what changed, roll them back or
simply refetch the cursor.
It's easier than it appears...
Very interesting thoughts, will surely think about them.
Thanks!
andre
Best
Klaus Major
[EMAIL PROTECTED]
http://www.major-k.de
___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
Re: A bit OT: handling multiple users in DB
I use a technique I learned from a well known dBase application called SBT. I add a numeric signature field (usually 2 digits) to every master table (defined as any table that is always updated whenever a transaction occurs). When a user opens a record for the purposes of editing, I get the value of the signature field for that record. If it's the highest value the field can contain, then someone else is in the middle of saving a transaction and has it locked for editing. (More on that later) But if it is NOT the highest value the field can hold, then I read the data for that record into variables. (Variables are nice because you can compare the values in the fields to their original values to see what changed). Now when the user tries to save the transaction, I get the value of the signature field again. If it is the same as the starting value, then I know no one has made any changes since I first read the data from the record. I then set the signature to 99 (or the highest value the field can contain). That is my record lock, so to speak. Because I check for this when I go to get the data, I effectively lock the record for that user. That way if someone comes along in the middle of a transaction update and tries to get the data, they are prevented by my own code until the other user's transaction is finished. I then update the record, and set the signature to the starting value + 1, freeing it up for future reads/updates. BUT if the initial signature and the present value is NOT the same then someone has updated the record in while I was editing it. At that point, depending on your application, you can present the user with a choice to cancel, or update their current information with the saved information and proceed as normal. A few caveats here. First, you may ask, why not just lock the record when the user reads the data in and keep it that way until the user saves or cancels the update? Okay, now the user leaves for the day, or crashes to desktop in the middle of an edit. The record remains locked forever, unless you run some maintenance script to unlock all records. Boo! Next, if you use loops to retry record access in the event the record is currently locked, you will not want to use endless loops as you can conceivably attain deadlock. Set a maximum number of times the loop will retry before it gives up and alerts the user. Consider also, that from the time the user clicks Save until the time the transaction is saved in all your tables, the application cannot for any reason stop, as in getting input from the user, because again, you can attain deadlock if the user fails to respond, or something crashes. Also, if there are many kinds of transactions which involve different sets of master tables, it is highly advisable to write a function which rechecks the signatures in all the affected tables and attempts to lock the records, before proceeding with any updates to any of the records. You don't want to be in the middle of saving a transaction only to find halfway in that someone posted changes to one of the records you needed to update. Lockdown every record that needs to be updated before proceeding with the transaction. Finally, a maintenance script to unlock all records in all tables is a must, because eventually something will go wrong in the middle of a transaction and strand records, preventing them from being updated. This all sounds like a lot of trouble I know, and for simple applications, one table to keep track of records that are allocated to a particular user is better, assuming you have a way of uniquely defining each connection. (Remember you may be able to login to your app with the same credentials from 2 or more workstations but they are separate connections nonetheless). But for a complex application, like for an integrated accounting solution, I think the signature method really works best. There is no need to track logged in users and deal with unexpected disruptions or duplicate credentials from different workstations. Also I would avoid any automatic record locking methods provided by the database mechanism you use, as these are not always reliable, and do not really deal with the problem of simultaneous multi-table transactions from multiple users. They only prevent data corruption from occurring by preventing simultaneous writes to a single table or database. Sorry for the lengthy post. Bob Sneidar IT Manager Logos Management Calvary Chapel CM On Sep 22, 2008, at 2:46 AM, Klaus Major wrote: Hi all, this is a bit off topic, but maybe someone can give me some helpful hints. Is someone of you working with databases with multiple users? If yes, how do you handle concurrent transactions? I mean how do you solve the possible overwriting of data when both users work with cursors and are allowed to update data and how do you update a
A bit OT: handling multiple users in DB
Bob Sneidar wrote: ...Sorry for the lengthy post. Not at all! This is a fascinating discussion. I was under the false impression that the automatic record-locking mechanisms would handle everything for me. Thanks for setting me straight! Looks like I have a lot more to learn before I dive into db design. -Kurt ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
