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]
