Re: LOCKS???

> On Nov 28, 2016, at 11:06 AM, Stuart Dallas <stuart@3ft9.com> wrote: > > On 28 November 2016 at 16:03:17, Stuart Dallas (stuart@3ft9.com) wrote: >> On 28 November 2016 at 15:19:12, Tedd Sperling (tedd.sperling@gmail.com) wrote: >>> This experiment demonstrates that LOCK TABLES does not work for me. >>> >>> My question is”Why?” >> >> The better question to ask is why are you trying to lock tables? There’s usually a number >> of better ways to achieve something that can be solved using table locking. What’s the actual >> operation you’re doing, or is it a purely academic question? >> > > Also, based on your restatement of the problem it would suggest you run the two scripts in > sequence, not in parallel. If that’s the case then the answer is clear: when a session is > terminated (i.e. you disconnect from the database whether explicitly, or due to an error, or because > the script ends) all locks obtained by that session are released. > > -Stuart -Stuart: To answer your first question: >> The better question to ask is why are you trying to lock tables? There’s usually a number >> of better ways to achieve something that can be solved using table locking. What’s the actual >> operation you’re doing, or is it a purely academic question? I have created a database of prospective employers (i.e., members) for the students of the college where I work. This database and my work is to be used to promote student employment. I have created a front-end that allows various “approved” users (i.e., faulty, administrators, and staff) to review and edit the database. “Approved" meaning I have created a way for users to be assigned various level access to the database (Read, Write, Create, and Assign/Delete/Change Levels for users). Hereinafter “user” shall be assumed to be at the highest level. My front-end also provides a way for users to connect members to both AOI (areas of interest — specialties defined by the employers) and Associations (groups defined by the college) and record these connections/relationships in the database. Both AOI's and Associations can be changed (i.e., updated, created, deleted) by the user and the user is presented with the assorted connections to various members with immediate links to their individual profile for editing. That way the user can see what AOI and Association connections exist between the various members — this is good when redefining AOI’s and Associations or reassigning various connections when deleting AOI's or Associations. My solution does not leave orphan relational records when deleting an element (AOI or Association) or reassigning either to a member. In addition, the college offers Events (time-date) where they feature different Association and/or AOIs for members to attend and advise. The actions of the members are also recorded in the database with respect to: a) IF they were sent an invitation; b) IF they RSVP’ed; c) and IF they attended the Event. My work also provided a Calendar where the user can create an Event (up to five per day, but we seldom have more than five per month). Furthermore, the front-end allows for sorting of members with respect to AOIs and/or Associations producing both emailing list and mailing list with the option to record these notices as “Invites” in Event invite tables. In addition, name tags can be created with the member’s picture OR business logo along with their name and business for attending Events. Also, the members data are shown to the user in various formats for editing (i.e., individual portfolios , list by AOI, list by Association, and assorted ways) — many of which allow AJAX editing. Lastly, every attribute of members can be searched via a wide variety of search mechanisms (specific, wild card, place holder, etc.). Obviously, much of the above is accomplished by relational tables. Now, considering there may be several users working on the database at the same time, I want to devise a way for users to do that without conflict (i.e., several users working on the same record at the same time). Sure, I can use transactions, but that simply assures the record being saved is actually recorded — but that’s not the issue here. Sure, I can simply allow only one user at a time to edit database, but I think this problem can be solved less harshly. So, I am currently looking at LOCKing down the tables involved with any specialized editing — such as editing a member, or editing an Event, or editing an AOI, or editing an Association, or connecting AOI/Associations where each of these operations require specific tables access in editing. What I am thinking is when a user shows interest in editing any table (i.e., users accesses a page that allows editing) to LOCK down the tables involved until such time the user is finished with editing (i.e., moves to a different page). In the event that two (or more) users access the same page, they all can read the page, but editing is limited to first one. The late comer(s) will be shown a pop-up that states they cannot edit the page at this time. Then when the first user leaves that page, then the next user will be told (another pop-up) the editing is open to them and then LOCKs are in place for them. I know this will require me to keep track of what users are looking at what pages, but that’s not too much of a problem provided I can actually LOCK down tables — and thus the reason for my question. — Your second Question: > Also, based on your restatement of the problem it would suggest you run the two scripts in > sequence, not in parallel. If that’s the case then the answer is clear: when a session is > terminated (i.e. you disconnect from the database whether explicitly, or due to an error, or because > the script ends) all locks obtained by that session are released. From my perspective, running the scripts in sequence or in parallel should be both accommodated with respect to different users accessing and editing the same table. Additionally, I am assuming that when the session is terminated so is the LOCK — but I have not proved that to myself — I can’t even get the damn LOCKs to work in the first place. I have read that LOCKs exist for only a short time, but that presents other problems, such as “Why did you lock the table in the first place, if the LOCK may expire before you are done editing? That kind of defeats the purpose of the LOCK, doesn’t it? So, I am not convinced that is true. I have also read that LOCKs can only be used in InnoDB engines — so I have changed my tables to that type, but the problem remains. I have also read about the dangers of LOCKing tables that may result in a deadlock (two, or more, operations require saving data to other LOCKed tables) but also I have read MySQL automatically allows the first user to LOCK their tables to win. Interesting, huh? Ok, I’ve read lot, but I still cannot get a single example of LOCK to work. --- Sorry for the long post, but you asked. Besides, you’re Stuart, I expect expert guidance and thoughtful insight from you. Cheers, tedd _______________ tedd sperling tedd.sperling@gmail.com