Hi,
I'm messing with this scenario:
- MySQL 5.0.x (it should work on 5.5 too)
- high concurrency jdbc client connections.

The code below is a "simplified" extract of the real application to
make easier the example which is like a job scheduler or, if you prefer,
a "process starter" :

Requirements (steps)
1) it takes the first 10 available jobs (row=job)
2) it marks "taken" those rows and pass them to the external jdbc client
(as resultset)
3) the external client processes those jobs and, when each job is
completed, deletes the corresponding row.

The SQL code below refers to the 1) and 2) step only:

CREATE TABLE  `tmp_table` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `field1` varchar(45) NOT NULL default '',
  `field2` varchar(45) NOT NULL default '',
  `status` smallint(5) unsigned NOT NULL default '0', -- 0 free, 1 busy
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;


DELIMITER $$
CREATE PROCEDURE  `pSelectTmpTable`()
BEGIN

START TRANSACTION;

SELECT id, field1, field2 FROM tmp_table WHERE status=0 LIMIT 10 FOR UPDATE;
UPDATE tmp_table SET status=1 WHERE status=0 LIMIT 10;

COMMIT;

END $$

Is it the right way to manipulate records in high concurrency context ?
Do particularly the clauses FOR UPDATE properly work under those
requirements ? Is there a more efficiently way to achieve this goal ?

Thanks in advance
Best regards,
M.

On Feb 10, 10:26=A0am, Mau C <nob...@hotmail.com> wrote:
> Hi,
> I'm messing with this scenario:
> - MySQL 5.0.x (it should work on 5.5 too)
> - high concurrency jdbc client connections.
>
> The code below is a "simplified" extract of the real application to
> make easier the example which is like a job scheduler or, if you prefer,
> a "process starter" :
>
> Requirements (steps)
> 1) it takes the first 10 available jobs (row=3Djob)
> 2) it marks "taken" those rows and pass them to the external jdbc client
> (as resultset)
> 3) the external client processes those jobs and, when each job is
> completed, deletes the corresponding row.
>
> The SQL code below refers to the 1) and 2) step only:
>
> CREATE TABLE =A0`tmp_table` (
> =A0 `id` int(10) unsigned NOT NULL auto_increment,
> =A0 `field1` varchar(45) NOT NULL default '',
> =A0 `field2` varchar(45) NOT NULL default '',
> =A0 `status` smallint(5) unsigned NOT NULL default '0', -- 0 free, 1 busy
> =A0 PRIMARY KEY =A0(`id`)
> ) ENGINE=3DInnoDB;
>
> DELIMITER $$
> CREATE PROCEDURE =A0`pSelectTmpTable`()
> BEGIN
>
> START TRANSACTION;
>
> SELECT id, field1, field2 FROM tmp_table WHERE status=3D0 LIMIT 10 FOR UP=
DATE;
> UPDATE tmp_table SET status=3D1 WHERE status=3D0 LIMIT 10;
>
> COMMIT;
>
> END $$
>
> Is it the right way to manipulate records in high concurrency context ?
> Do particularly the clauses FOR UPDATE properly work under those
> requirements ? Is there a more efficiently way to achieve this goal ?
>
> Thanks in advance
> Best regards,
> M.

You can actually achieve a similar result without the need for
transactions.

Suppose that each client has a unique client_id. The status field
holds 0 if the job has not been taken or the handling client_id if it
has been.

You therefore do
UPDATE tmp_table SET status=3D{client_id} WHERE status=3D0 LIMIT 10;
SELECT id, field1, field2 FROM tmp_table WHERE status=3D{client_id}

The benefit of this is that you know which client is handling which
jobs.

Il 10/02/2011 13.47, Captain Paralytic ha scritto:
[...]
> You therefore do
> UPDATE tmp_table SET status={client_id} WHERE status=0 LIMIT 10;
> SELECT id, field1, field2 FROM tmp_table WHERE status={client_id}
> 
> The benefit of this is that you know which client is handling which
> jobs.

Ok,
I like this point of view. Thank you.
Anyway, does this way prevent from any deadlock and/or row contention ?

On Feb 10, 2:02=A0pm, Mau C <nob...@hotmail.com> wrote:
> Il 10/02/2011 13.47, Captain Paralytic ha scritto:
> [...]
>
> > You therefore do
> > UPDATE tmp_table SET status=3D{client_id} WHERE status=3D0 LIMIT 10;
> > SELECT id, field1, field2 FROM tmp_table WHERE status=3D{client_id}
>
> > The benefit of this is that you know which client is handling which
> > jobs.
>
> Ok,
> I like this point of view. Thank you.
> Anyway, does this way prevent from any deadlock and/or row contention ?

Yes because it will only choose rows that have the status =3D 0. If that
wasn't the case, then there would be no point in having the WHERE
clause on a UPDATE statement.

Il 10/02/2011 15.19, Captain Paralytic ha scritto:
> Yes because it will only choose rows that have the status = 0. If that
> wasn't the case, then there would be no point in having the WHERE
> clause on a UPDATE statement.

Ok,
but, without an isolated transaction, imagine to have something like this

TIME    CLIENT xx     CLIENT yy
----    ---------     ---------
 0      UPDATE
 1                    UPDATE
 2      SELECT
 3                    SELECT

Which subset of rows will get the client xx ?
Client yy may override the xx updates... ? If UPDATE is perfectly
"atomic", the answer is definitely no.
If the answer is yes, for sure client xx won't never get wrong rows but
maybe less then 10...

On Feb 10, 2:30=A0pm, Mau C <nob...@hotmail.com> wrote:
> Il 10/02/2011 15.19, Captain Paralytic ha scritto:
>
> > Yes because it will only choose rows that have the status =3D 0. If tha=
t
> > wasn't the case, then there would be no point in having the WHERE
> > clause on a UPDATE statement.
>
> Ok,
> but, without an isolated transaction, imagine to have something like this
>
> TIME =A0 =A0CLIENT xx =A0 =A0 CLIENT yy
> ---- =A0 =A0--------- =A0 =A0 ---------
> =A00 =A0 =A0 =A0UPDATE
> =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0UPDATE
> =A02 =A0 =A0 =A0SELECT
> =A03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0SELECT
>
> Which subset of rows will get the client xx ?
> Client yy may override the xx updates... ? If UPDATE is perfectly
> "atomic", the answer is definitely no.
> If the answer is yes, for sure client xx won't never get wrong rows but
> maybe less then 10...

I have told you that it works and it does. I cannot tell you which
clients will get which rows, but I can tell you that, providing that
there are sufficient rows to allow for 10 rows per client, they will
each get their own 10 rows.

Mau C <nobody@hotmail.com> wrote:

> I'm messing with this scenario:
> - MySQL 5.0.x (it should work on 5.5 too)
> - high concurrency jdbc client connections.
>
> The code below is a "simplified" extract of the real application to
> make easier the example which is like a job scheduler or, if you prefer,
> a "process starter" :
>
> Requirements (steps)
> 1) it takes the first 10 available jobs (row=job)
> 2) it marks "taken" those rows and pass them to the external jdbc client
> (as resultset)
> 3) the external client processes those jobs and, when each job is
> completed, deletes the corresponding row.

OK so far.

> The SQL code below refers to the 1) and 2) step only:
>
> CREATE TABLE  `tmp_table` (
>   `id` int(10) unsigned NOT NULL auto_increment,
>   `field1` varchar(45) NOT NULL default '',
>   `field2` varchar(45) NOT NULL default '',
>   `status` smallint(5) unsigned NOT NULL default '0', -- 0 free, 1 busy
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB;

This is far from perfect, but read on.

> CREATE PROCEDURE  `pSelectTmpTable`()
> BEGIN
> START TRANSACTION;
> SELECT id, field1, field2 FROM tmp_table WHERE status=0 LIMIT 10 FOR UPDATE;
> UPDATE tmp_table SET status=1 WHERE status=0 LIMIT 10;
> COMMIT;
> END $$

Why using a procedure? Makes no sense IMHO.

> Is it the right way to manipulate records in high concurrency context ?

No. The above makes completely no sense. For several reasons.

*If* you do it in two stages
1. lock the rows with SELECT ... FOR UPDATE
2. modify those records
*then* you must do it right and do the UPDATE based on the id(s)
delivered by the first SELECT.

However it's much easier to do it how the captain suggested: give each
scheduler it's own unique id and reserve the rows with

UPDATE jobs SET taker=<my_id> WHERE taker=0 LIMIT 10

Then later select the reserved jobs with SELECT ... WHERE taker=<my_id>
and finally DELETE the rows based on id.


But there is more to it. In a highly concurrent situation you want not
only short transactions, you also want each transaction to lock as few
records as possible. And here your design above meets a problem in
MySQL. An UPDATE puts locks on all rows that it modifies. But if MySQL
has to do a scan to find those rows (table scan or index scan) then it
locks *all* records as it scans them.

Now look at the update statement again:

UPDATE jobs SET taker=<my_id> WHERE taker=0

since there is no index on the `taker` column (you called it `status`)
MySQL has to do a table scan and will thus lock all rows from the
beginning of the table up to the 10th record with `taker`=0.
Conclusion: you definitely want an index on the `taker` column.

Finally: the obligatory RTFM!

http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html


XL

Il 10/02/2011 22.22, Axel Schwenke ha scritto:
[...]

Thank you both. I definitively agree ...
As <my_id> I can use CONNECTION_ID()

Regards,
M.

Il 11/02/2011 8.41, Mau C ha scritto:
> Il 10/02/2011 22.22, Axel Schwenke ha scritto:
> [...]
> 
> Thank you both. I definitively agree ...
> As <my_id> I can use CONNECTION_ID()

I notice right now that it is not easy to find a *unique* <my_id> ...

CONNECTION_ID() returns an unique client id ... But each client may use
the same connection id untile the connection pool decides to invalidate it.
So, the scenario is :
- A single client has a connection id xx
- A client thread FOO retreives <n> rows marked with status=xx and puts
into a buffer
- Another client thread BAR processes independently those <n> rows/jobs
reading from the buffer and when completed deletes each row; in the
meanwhile FOO tries to fill again the buffer but.... it marks others <n>
rows and retreives <n> + "the previous already marked" which are not
still processed (and deleted) ...
- The thread BAR may get and process from buffer the same rows/jobs again.

So the problem is to find an unique <my_id> which won't cause contention
in a high concurrency scenario...

Any rand() or hash code function could be the solution... but it does
not theorically prevent from contention...

On Feb 11, 10:41=A0am, Mau C <nob...@hotmail.com> wrote:
> Il 11/02/2011 8.41, Mau C ha scritto:
>
> > Il 10/02/2011 22.22, Axel Schwenke ha scritto:
> > [...]
>
> > Thank you both. I definitively agree ...
> > As <my_id> I can use CONNECTION_ID()
>
> I notice right now that it is not easy to find a *unique* <my_id> ...
>
> CONNECTION_ID() returns an unique client id ... But each client may use
> the same connection id untile the connection pool decides to invalidate i=
t.
> So, the scenario is :
> - A single client has a connection id xx
> - A client thread FOO retreives <n> rows marked with status=3Dxx and puts
> into a buffer
> - Another client thread BAR processes independently those <n> rows/jobs
> reading from the buffer and when completed deletes each row; in the
> meanwhile FOO tries to fill again the buffer but.... it marks others <n>
> rows and retreives <n> + "the previous already marked" which are not
> still processed (and deleted) ...
> - The thread BAR may get and process from buffer the same rows/jobs again=
..
>
> So the problem is to find an unique <my_id> which won't cause contention
> in a high concurrency scenario...
>
> Any rand() or hash code function could be the solution... but it does
> not theorically prevent from contention...

You haven't give us any details of what the clients are, but there is
normally some sort of task number or session id that is unique to a
client. If you could tell us something about the clients, maybe we
could help further.

On Fri, 11 Feb 2011 03:46:53 -0800 (PST), Captain Paralytic wrote:
> On Feb 11, 10:41?am, Mau C <nob...@hotmail.com> wrote:
>> Il 11/02/2011 8.41, Mau C ha scritto:
>>
>> > Il 10/02/2011 22.22, Axel Schwenke ha scritto:
>> > [...]
>>
>> > Thank you both. I definitively agree ...
>> > As <my_id> I can use CONNECTION_ID()
>>
>> I notice right now that it is not easy to find a *unique* <my_id> ...
>>
>> CONNECTION_ID() returns an unique client id ... But each client may use
>> the same connection id untile the connection pool decides to invalidate it.
>> So, the scenario is :
>> - A single client has a connection id xx
>> - A client thread FOO retreives <n> rows marked with status=xx and puts
>> into a buffer
>> - Another client thread BAR processes independently those <n> rows/jobs
>> reading from the buffer and when completed deletes each row; in the
>> meanwhile FOO tries to fill again the buffer but.... it marks others <n>
>> rows and retreives <n> + "the previous already marked" which are not
>> still processed (and deleted) ...
>> - The thread BAR may get and process from buffer the same rows/jobs again.
>>
>> So the problem is to find an unique <my_id> which won't cause contention
>> in a high concurrency scenario...
>>
>> Any rand() or hash code function could be the solution... but it does
>> not theorically prevent from contention...
>
> You haven't give us any details of what the clients are, but there is
> normally some sort of task number or session id that is unique to a
> client. If you could tell us something about the clients, maybe we
> could help further.

Yup. "Subject to host application determination" is how it gets written
in my process doco, but it essentially means "You're on your own,
sucker." And one, at this point, would tend to want it to be be
completely independant of the DB's features anyway, for portability
reasons. 

-- 
This was, apparently, beyond her ken.  So far beyond her ken that she 
was well into barbie territory.
              -- J. D. Baldwin

Il 11/02/2011 12.46, Captain Paralytic ha scritto:
[...]
> You haven't give us any details of what the clients are, but there is
> normally some sort of task number or session id that is unique to a
> client. If you could tell us something about the clients, maybe we
> could help further.

You are right.

The client software is :

 - A master thread FOO which makes a loop as
a) gets <x> free rows / jobs, sets them as "taken" and puts them into a
buffer;
b) delete completed rows / jobs.

 - A fixed number of slave threads BAR(n) (n= number of client cores).
Each thread BAR retreives (and deletes) a single row / job from the
buffer, processes the row / job and when completed sends the "completed"
information to the FOO thread.

So,
according with your suggestion the thread FOO should check if the buffer
is empty and then gets the rows. In this case I can use a unique id
(such as connection id) because I'm sure I won't have any already
"taken" unprocessed rows from db.
So, the a) step could be
a) if the buffer is EMPTY and all rows are been processed / deleted then
gets <x> free...bla bla...

In a "desired" world I would always have a "filled" buffer to avoid
performance lacks (I don't want FOO waits so long the BAR(n) threads
clean up the buffer).
So, the FOO step a) should be
a) if the buffer size < MAX_BUFFER_LIMIT then gets <x> rows... bla bla

I hope this makes clear the scenario.

Best regards,
M.

On Feb 11, 1:36=A0pm, Mau C <nob...@hotmail.com> wrote:
> Il 11/02/2011 12.46, Captain Paralytic ha scritto:
> [...]
>
> > You haven't give us any details of what the clients are, but there is
> > normally some sort of task number or session id that is unique to a
> > client. If you could tell us something about the clients, maybe we
> > could help further.
>
> You are right.
>
> The client software is :
>
> =A0- A master thread FOO which makes a loop as
> a) gets <x> free rows / jobs, sets them as "taken" and puts them into a
> buffer;
> b) delete completed rows / jobs.
>
> =A0- A fixed number of slave threads BAR(n) (n=3D number of client cores)=
..
> Each thread BAR retreives (and deletes) a single row / job from the
> buffer, processes the row / job and when completed sends the "completed"
> information to the FOO thread.
Why doesn't each thread pick up its own job from the table. ISTM that
the table already supplies a "buffer" for you.

Il 11/02/2011 14.48, Captain Paralytic ha scritto:
> Why doesn't each thread pick up its own job from the table. ISTM that
> the table already supplies a "buffer" for you.

The master FOO thread is unique because I want to avoid jdbc read /
write concurrency inside the same client. Slave BAR(n) threads need to
read / write from common (synchronized) job list, so I want buffers.

The goal should be :
- FOO gets <x> rows where <x> is not so big to avoid a huge table lock
and network usage, not so small to avoid too much read attempts.
- MAX_BUFFER_SIZE >= <x> to assure to the BAR(n) threads always
something to do.

M.

On Feb 11, 2:14=A0pm, Mau C <nob...@hotmail.com> wrote:
> Il 11/02/2011 14.48, Captain Paralytic ha scritto:
>
> > Why doesn't each thread pick up its own job from the table. ISTM that
> > the table already supplies a "buffer" for you.
>
> The master FOO thread is unique because I want to avoid jdbc read /
> write concurrency inside the same client. Slave BAR(n) threads need to
> read / write from common (synchronized) job list, so I want buffers.
>
> The goal should be :
> - FOO gets <x> rows where <x> is not so big to avoid a huge table lock
> and network usage, not so small to avoid too much read attempts.
> - MAX_BUFFER_SIZE >=3D <x> to assure to the BAR(n) threads always
> something to do.
>
> M.

I get the feeling that you are prematurely optimising.

Il 11/02/2011 15.32, Captain Paralytic ha scritto:
> I get the feeling that you are prematurely optimising.

Well,
that's the architecture. <x> and MAX_BUFFER_SIZE are still undefined :-)

On 2/11/2011 9:32 AM, Captain Paralytic wrote:
> On Feb 11, 2:14 pm, Mau C<nob...@hotmail.com>  wrote:
>> Il 11/02/2011 14.48, Captain Paralytic ha scritto:
>>
>>> Why doesn't each thread pick up its own job from the table. ISTM that
>>> the table already supplies a "buffer" for you.
>>
>> The master FOO thread is unique because I want to avoid jdbc read /
>> write concurrency inside the same client. Slave BAR(n) threads need to
>> read / write from common (synchronized) job list, so I want buffers.
>>
>> The goal should be :
>> - FOO gets<x>  rows where<x>  is not so big to avoid a huge table lock
>> and network usage, not so small to avoid too much read attempts.
>> - MAX_BUFFER_SIZE>=<x>  to assure to the BAR(n) threads always
>> something to do.
>>
>> M.
>
> I get the feeling that you are prematurely optimising.

Paul,

I've been following this thread also, and have to agree with you.  Often 
the simplest solutions are the best; making the solution needlessly 
complex not only creates a headache nightmare, but can  decrease 
performance.

For instance - how often do jobs get scheduled?  How long does it take 
to start them?  If they are scheduling 5 jobs/sec but can start 50 
job/sec with a single thread, all this complexity is completely unnecessary.

And if they're concerned about performance, jdbc is NOT the way to go!

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 2/11/2011 9:57 AM, Mau C wrote:
> Il 11/02/2011 15.32, Captain Paralytic ha scritto:
>> I get the feeling that you are prematurely optimising.
>
> Well,
> that's the architecture.<x>  and MAX_BUFFER_SIZE are still undefined :-)

I agree with Paul here.  If that's the architecture, you need to 
reexamine the architecture.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Il 11/02/2011 16.58, Jerry Stuckle ha scritto:
> On 2/11/2011 9:57 AM, Mau C wrote:
>> Il 11/02/2011 15.32, Captain Paralytic ha scritto:
>>> I get the feeling that you are prematurely optimising.
>>
>> Well,
>> that's the architecture.<x>  and MAX_BUFFER_SIZE are still undefined :-)
> 
> I agree with Paul here.  If that's the architecture, you need to
> reexamine the architecture.
> 

I did.
After some preliminay test I saw there's no any special reason to delay
the DELETE from Tmp_Table.

The DELETE was there as error recovery: if the job execution fails I do
not DELETE but UPDATE SET status = 0 its id and run it again.
Now, if the job execution fails, I do the simply INSERT
field1,field2,... again.

So, according with your suggestions, the stored can be
1) @my_id = CONNECTION_ID()
2) UPDATE SET status = @my_id
3) retrevied rows
4) DELETE rows WHERE status = @my_id

Jdbc and stored procedures are necessary due to the high portability
requirements. In fact I've also submitted this scenario on the Oracle e
SQL Server groups :-)

Any total run might count up to 500000 job executions. Each job
execution depends on the input parameters and should last between 1 and
50 s. The network traffic over the jdbc connection could be the
bottleneck so I've decided to have buffers and reduce open/close/sql
statements calls. Ok, buffers mean RAM which means no transaction-safe
and possibly data lacks... But I can't think about everythink today,
it's S.Valentine :-)

Thanks for all your support.
M.

On 2/14/2011 3:19 AM, Mau C wrote:
> Il 11/02/2011 16.58, Jerry Stuckle ha scritto:
>> On 2/11/2011 9:57 AM, Mau C wrote:
>>> Il 11/02/2011 15.32, Captain Paralytic ha scritto:
>>>> I get the feeling that you are prematurely optimising.
>>>
>>> Well,
>>> that's the architecture.<x>   and MAX_BUFFER_SIZE are still undefined :-)
>>
>> I agree with Paul here.  If that's the architecture, you need to
>> reexamine the architecture.
>>
>
> I did.
> After some preliminay test I saw there's no any special reason to delay
> the DELETE from Tmp_Table.
>
> The DELETE was there as error recovery: if the job execution fails I do
> not DELETE but UPDATE SET status = 0 its id and run it again.
> Now, if the job execution fails, I do the simply INSERT
> field1,field2,... again.
>
> So, according with your suggestions, the stored can be
> 1) @my_id = CONNECTION_ID()
> 2) UPDATE SET status = @my_id
> 3) retrevied rows
> 4) DELETE rows WHERE status = @my_id
>
> Jdbc and stored procedures are necessary due to the high portability
> requirements. In fact I've also submitted this scenario on the Oracle e
> SQL Server groups :-)
>
> Any total run might count up to 500000 job executions. Each job
> execution depends on the input parameters and should last between 1 and
> 50 s. The network traffic over the jdbc connection could be the
> bottleneck so I've decided to have buffers and reduce open/close/sql
> statements calls. Ok, buffers mean RAM which means no transaction-safe
> and possibly data lacks... But I can't think about everythink today,
> it's S.Valentine :-)
>
> Thanks for all your support.
> M.

I wasn't referring to delaying the delete on the tmp table - I was 
referring to your entire architecture.  And I'd say the exact same thing 
if you were using Oracle, SQL Server or DB2 (all of which I've used over 
the years).

As for portability - there are other ways to get portability than using 
jdbc.  And there are few things slower than jdbc.

But you don't really know if the network connection is a bottleneck or 
not.  With jdbc, I highly suspect it will not be.  But without solid 
information, you are prematurely optimizing - which almost always leads 
to a substandard solution.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================