Locking Records - Multiple Users

Scriptcase 8 Discussion
#1

What is the best way in SC to make it so multiple users accessing the same database do not end up editing the same records at the same time?
Im developing an order processing application and when a user is working on a particular order, I dont want others users to be able to access it.

Should I just put some sort of flag in the database that I flip upon opening the record?
What do you feel the best way would be?
Im looking for any and all opinions.

Thanks!

#2

That depends on the type of database. Sometimes you can flag it yourself, sometimes the transaction mechanism protects it, sometimes you can ‘select for update …’ records to lock etc.

#3

I’ve always assumed MySQL automatically does when in transaction mode/ InnoDB. That’s the case right?

#4

I have never had troubles with multiple users using MySQL database. I do not know how it is with other types of db.

#5

Example: I have a list of orders on a grid. When a user clicks to edit the order they are at a screen that allows them to modify aspects of the order. While they are on that screen I do not want to allow another user see the same order. I want it to be “In Use”.
Albert: please explain further the “Select for Update” you mention. I have been reading in the help about the “Transaction Mechanism” but am still unclear on how best to do this.

Thank you everyone for all your help!

#6

[QUOTE=yourguide;34485]Example: I have a list of orders on a grid. When a user clicks to edit the order they are at a screen that allows them to modify aspects of the order. While they are on that screen I do not want to allow another user see the same order. I want it to be “In Use”.
Albert: please explain further the “Select for Update” you mention. I have been reading in the help about the “Transaction Mechanism” but am still unclear on how best to do this.

Thank you everyone for all your help![/QUOTE]

Along those lines, this looks promising and applicable to many dbs: http://stackoverflow.com/questions/10935850/when-to-use-select-for-update. Though I’ve never used “…for update” in SC.

Oops, I’m not Albert am I!!!

#7

[QUOTE=scriptcaser;34507]Along those lines, this looks promising and applicable to many dbs: http://stackoverflow.com/questions/10935850/when-to-use-select-for-update. Though I’ve never used “…for update” in SC.

Oops, I’m not Albert am I!!![/QUOTE]

Thanks scriptcaser… any and all information is helpful. I am now thinking that perhaps using Session Variables would be the correct way to maintain the user’s state?
Anyone else develop an application like this for multiple users where you need to not allow multiple users to edit the same record?

#8

[QUOTE=yourguide;34868]Thanks scriptcaser… any and all information is helpful. I am now thinking that perhaps using Session Variables would be the correct way to maintain the user’s state?
Anyone else develop an application like this for multiple users where you need to not allow multiple users to edit the same record?[/QUOTE]

I would like to do so, but didn’t have time to go through it yet, Albert is correct, if your database doesn’t support it then you need to implement it, I use mysql and I think that is supported by default, but anyway there is some work must be done from sc itself which nobody yet could give us tutorial on that

why dont we ask the guys over the webinars to explain it somehow!? what you think?

#9

Great idea MikeDE! They are always looking for good suggestions for useful webinars.

#10

Be aware that there are several scenario’s that covers the matter. There is a select for update in mysql, and it might be possible to use that. But the problem is that if the user keeps the record locked for whatever reason the other user has no clue.

If it’s realy necessary to lock a certain record for update I would advise the following (custom) scenario (just a raw thought, never had the need of using it)

  1. create a table ‘mylocks’ containing a few fields: tablename, primary key, lockdatetime, owner.
  2. delete all entries in this mylocks table that are locked but exceeds a certain update time, i.e. 10 minutes.
  3. When a record needs to be edited you look into this ‘mylocks’ table to see if it’s there. If it is, you are not allowed to edit. If it isn’t create a new entry and allow the record to be edited.
  4. Before update check if the lock still persists n the table. if it is, update. If it is, but not owned by the user requesting it (because it was released after 10 minutes and taken by someone else) generate an errormessage. Otherwize update the record.

I think that this would be a pretty simple and easy way to implement the custom lock? You can even report who currently has the record for update…

#11

[QUOTE=aducom;34943]Be aware that there are several scenario’s that covers the matter. There is a select for update in mysql, and it might be possible to use that. But the problem is that if the user keeps the record locked for whatever reason the other user has no clue.

If it’s realy necessary to lock a certain record for update I would advise the following (custom) scenario (just a raw thought, never had the need of using it)

  1. create a table ‘mylocks’ containing a few fields: tablename, primary key, lockdatetime, owner.
  2. delete all entries in this mylocks table that are locked but exceeds a certain update time, i.e. 10 minutes.
  3. When a record needs to be edited you look into this ‘mylocks’ table to see if it’s there. If it is, you are not allowed to edit. If it isn’t create a new entry and allow the record to be edited.
  4. Before update check if the lock still persists n the table. if it is, update. If it is, but not owned by the user requesting it (because it was released after 10 minutes and taken by someone else) generate an errormessage. Otherwize update the record.

I think that this would be a pretty simple and easy way to implement the custom lock? You can even report who currently has the record for update…[/QUOTE]

Great idea Albert, thanks a lot, really, it also make sense and easier than going into db locking stuff, I will try to do it ASAP, hopefully works as required, will let you guys know if works, but i’m not sure when it will be though :frowning:

#12

There’s a small issue I overlooked. If someone is retrieving the record just for read the lock should not apply. So you need a button of some kind to enable locking or create a separate application for that.

#13

Just another recommendation… add functionality for an administrator to override the lock.

Regards

#14

[QUOTE=kafecadm;35019]Just another recommendation… add functionality for an administrator to override the lock.

Regards[/QUOTE]

thanks dude, let us finish the main dilemma yet :smiley:

thanks also Albert, you are right.