I like this solution. Gonna cogitate on it for a while.
=C=
----- Original Message -----
From: "GERST, MICHAEL (SBCSI)" <[EMAIL PROTECTED]>
To: "'Cal Evans'" <[EMAIL PROTECTED]>; "gerald_clark"
<[EMAIL PROTECTED]>
Cc: "MySQL" <[EMAIL PROTECTED]>
Sent: Wednesday, April 02, 2003 11:59 AM
Subject: RE: How Many
> Just noticed this thread.
>
> Here is another method, still using the 'userid' method, but slightly
> differently; it claims a job immediately. You then have to run a select
by
> your own 'userid' to find out what you claimed:
>
> Assumptions:
> This assumes that the following table exists, and that unique_job_id is an
> int, and a primary key. This algorithm also assumes that the jobs should
be
> working in ascending order (i.e. job 1234 gets processed before 1235)
> Also assumes using MySQL Server 4.1 (won't work with 3.xx.xx)
>
> TABLE WorkTable{
> userid varchar ALLOW NULL
> unique_job_id int NOT NULL PRIMARY KEY
> }
>
> Statements:
>
> UPDATE WorkTable
> SET userid = 'my_unique_name'
> WHERE userid=NULL and
> unique_job_id=( SELECT unique_job_id
> FROM WorkTable
> WHERE userid=NULL
> ORDER BY unique_job_id ASC
> LIMIT 1);
> SELECT unique_job_id
> FROM WorkTable
> WHERE userid='my_unique_name';
>
> This help a bit? You may also be able to use an API function like
> get_last_update or mysql_affected_rows to gather information on what the
> UPDATE affected rather than another select. This was just quick and
> generic.
>
> -Mike
>
> -----Original Message-----
> From: Cal Evans [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 02, 2003 11:12 AM
> To: gerald_clark
> Cc: MySQL
> Subject: Re: How Many
>
> last man wins.
>
> ----- Original Message -----
> From: "gerald_clark" <[EMAIL PROTECTED]>
> To: "Cal Evans" <[EMAIL PROTECTED]>
> Cc: "MySQL" <[EMAIL PROTECTED]>
> Sent: Wednesday, April 02, 2003 11:10 AM
> Subject: Re: How Many
>
>
> > A Checks for empty
> > B Checks for empty
> > A Updates
> > A Reads ( and owns record )
> > B Updates
> > B Reads ( and owns record )
> >
> > Now What?
> >
> > Cal Evans wrote:
> >
> > >No. You check for empty. update, check again to make sure you now own
> it.
> > >The userid is either going to be you OR someone else. not both. It
it's
> you
> > >then you own the record. If it's someone else then someone slipped in
> and
> > >grabbed it from you.
> > >
> > >=C=
> > >----- Original Message -----
> > >From: "gerald_clark" <[EMAIL PROTECTED]>
> > >To: "Cal Evans" <[EMAIL PROTECTED]>
> > >Cc: "Ed Kiefer" <[EMAIL PROTECTED]>; "MySQL"
<[EMAIL PROTECTED]>
> > >Sent: Wednesday, April 02, 2003 9:22 AM
> > >Subject: Re: How Many
> > >
> > >
> > >
> > >
> > >>Cal Evans wrote:
> > >>
> > >>
> > >>
> > >>>Ed,
> > >>>
> > >>>1: It depends on your hardware. I don't think MySQL imposes a
> > >>>
> > >>>
> > >restriction.
> > >
> > >
> > >>>2: It doesn't work that way. It's not like VFP or Access where you
> > >>>
> > >>>
> > >'edit' a
> > >
> > >
> > >>>record. You can SELECT the contents of a record, you can manipulate
> them
> > >>>
> > >>>
> > >and
> > >
> > >
> > >>>then you can UPDATE that record (assuming you have a primary key) but
> the
> > >>>last person to issue an update is the one who wins.
> > >>>
> > >>>One thing I've done in the past is use 'soft locks'. Basically if
you
> > >>>
> > >>>
> > >HAVE
> > >
> > >
> > >>>to insure that while someone has requested the right to edit a
record,
> no
> > >>>one else can do it you have to enforce it in your own software.
> > >>>
> > >>>A soft lock is simply a couple of fields in a record. usually a
userID
> > >>>
> > >>>
> > >and a
> > >
> > >
> > >>>timedate field. When someone requests permission to edit a record I
> do:
> > >>>
> > >>>1: Make sure the userid field is empty
> > >>>2: UPDATE the record with this users userid and the current timedate.
> > >>>3: SELECT the record again and make sure that this user's userid is
the
> > >>>current one. (Keeps someone from slipping in on ya.)
> > >>>
> > >>>
> > >>>
> > >>There is still a window of opportunity for an error here.
> > >>You can both check for empty, then both update and read.
> > >>Now two users believe they have the record.
> > >>Update therecord set userid=concat(userid,'|','me') where
id=thisnumber;
> > >>select userid from the record where id=thisnumber.
> > >>
> > >>Now:
> > >>If the user is '|me' , I am the owner.
> > >>If it is '|me|someoneelse' , I am still the owner.
> > >>If it is '|someoneelse|me', I am not the owner.
> > >>
> > >>
> > >>
> > >>>4: Allow them to edit the record
> > >>>5: Commit the record and clear the userid.
> > >>>
> > >>>If there IS a userid already on the record, I check the timestamp.
If
> > >>>
> > >>>
> > >it's
> > >
> > >
> > >>>more than x time units (10 minutes, 2 hours, 3 days, etc) old, I
> > >>>
> > >>>
> > >invalidate
> > >
> > >
> > >>>the lock by going to step 2 anyhow.
> > >>>
> > >>>Clear as mud?
> > >>>
> > >>>=C=
> > >>>----- Original Message -----
> > >>>From: "Ed Kiefer" <[EMAIL PROTECTED]>
> > >>>To: "MySQL" <[EMAIL PROTECTED]>
> > >>>Sent: Tuesday, April 01, 2003 7:40 AM
> > >>>Subject: How Many
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>>How many people can be accessing the same mysql database at the same
> > >>>>
> > >>>>
> > >time?
> > >
> > >
> > >>>>Can several people work on the same record at the same time?
> > >>>>
> > >>>>
> > >>>>--
> > >>>>MySQL General Mailing List
> > >>>>For list archives: http://lists.mysql.com/mysql
> > >>>>To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>
> > >>--
> > >>MySQL General Mailing List
> > >>For list archives: http://lists.mysql.com/mysql
> > >>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
