Concurrent Insert - Locking the database

I have a perl function which inserts a record into mysql database. The
function looks something like

sub insertRecord
{
$pkey = getLatestKey() + 1;
executeQuery("insert into tablename values($pkey,......)");
}

getLatestKey()
{
$a = executeQuery("select max(pkey) from tablename");
return $a;
}

This is fine for non-concurrent insert. But, multiple users will be
using this at the same time to insert records, which may create problem.

Suppose max(pkey) is 1500. If two users executes concurrently, user 1
must get 1501 and user 2 must get 1502. How to achieve this ?

Regards,
Pradeep


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Concurrent Insert - Locking the database

On Tue, 22 Feb 2005 10:45:34 -0800, Pradeep wrote:
> I have a perl function which inserts a record into mysql database. The
> function looks something like
>
> sub insertRecord
> {
> $pkey = getLatestKey() + 1;
> executeQuery("insert into tablename values($pkey,......)");
> }
>
> getLatestKey()
> {
> $a = executeQuery("select max(pkey) from tablename");
> return $a;
> }

$a = executeQuery("SELECT LAST_INSERT_ID() AS max_pkey FROM tablename");

This works if you are doing single inserts as you would expect; for
multiple-inserts within a single INSERT command, you get the FIRST id
number that was generated for that block (to which you add in the
number of records you just inserted).

Reference:
http://dev.mysql.com/doc/mysql/en/getting-unique-id.html

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Concurrent Insert - Locking the database

> I have a perl function which inserts a record into mysql database. The
> function looks something like
>
> sub insertRecord
> {
> $pkey = getLatestKey() + 1;
> executeQuery("insert into tablename values($pkey,......)");
> }
>
> getLatestKey()
> {
> $a = executeQuery("select max(pkey) from tablename");
> return $a;
> }
>
> This is fine for non-concurrent insert. But, multiple users will be
> using this at the same time to insert records, which may create problem.
>
> Suppose max(pkey) is 1500. If two users executes concurrently, user 1
> must get 1501 and user 2 must get 1502. How to achieve this ?
>
Define pkey field as "autoincrement" and this must be a primary key. Your
function will be

sub insertRecord
{
executeQuery("insert into tablename fields(filed1,field2...)
values($value1,$value2......)");
}

where nothing will be inserted into pkey filed!
Look for "autoincrement" keyword in your MySQL manual ;-)

If you can't to use autoincrement (may be that cases), you must
LOCK TABLE tablename WRITE
before insert and unlock it after.

Petr Vileta, Czech republic


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org