Tcl - Managing indices with MySQLTcl

Author

Managing indices with MySQLTcl

Chris Nelson

2004-05-17, 10:31 pm

This is a little OT. Sorry.

I have two MySQL tables that need to have mutually exclusive IDs
(because a third table relates to both of them). (Maybe there's a
better design.) I've created a third table to manage the index:

CREATE TABLE idControl (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
);

In SQL, I'd get the next id with something like:

insert into idcontrol (id) values (null); delete from idcontrol;
select last_insert_id();

which, I hope, being strung together like that, will happen
atomically. But I'm stumped how to do that in Tcl (using MySQLTcl).
MySQLTcl gives me mysqlsel (which doesn't seem to allow compound
statements like above) and mysqlexec (which throws away SELECT
results).

Can anyone point me at a solution (or a better approach)?

tunity5@yahoo.com

2004-05-18, 5:31 am

cnelson@nycap.rr.com (Chris Nelson) wrote in message news:<9245c0ba.0405171705.67a25919@posting.google.com>...
> This is a little OT. Sorry.
>
> I have two MySQL tables that need to have mutually exclusive IDs
> (because a third table relates to both of them). (Maybe there's a
> better design.) I've created a third table to manage the index:
>
> CREATE TABLE idControl (
> id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
> );
>
> In SQL, I'd get the next id with something like:
>
> insert into idcontrol (id) values (null); delete from idcontrol;
> select last_insert_id();
>
> which, I hope, being strung together like that, will happen
> atomically. But I'm stumped how to do that in Tcl (using MySQLTcl).
> MySQLTcl gives me mysqlsel (which doesn't seem to allow compound
> statements like above) and mysqlexec (which throws away SELECT
> results).
>
> Can anyone point me at a solution (or a better approach)?

I think your goal is to retrieve a sequence of numbers (with each
successive call) to use as the id of your tables in mysql. The code
you posted is ambiguous but instead, try the following:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

Then the table can be used to generate sequence numbers like this (and
you can use the mysqltcl functions you mentioned above for these):

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT id FROM sequence;

Chris Nelson

2004-05-18, 10:46 am

tunity5@yahoo.com wrote in message news:<32bcd267.0405180025.61e0b16d@posting.google.com>...
> cnelson@nycap.rr.com (Chris Nelson) wrote in message news:<9245c0ba.0405171705.67a25919@posting.google.com>...
>
> I think your goal is to retrieve a sequence of numbers (with each
> successive call) to use as the id of your tables in mysql. ...

Yes.

> mysql> CREATE TABLE sequence (id INT NOT NULL);
> mysql> INSERT INTO sequence VALUES (0);

OK.

> Then the table can be used to generate sequence numbers like this (and
> you can use the mysqltcl functions you mentioned above for these):
>
> mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
> mysql> SELECT id FROM sequence;

But the problem is that that's _two_ statements. If I code that in
MySQLTcl, there's a chance that two users are going to get the same
ID, no? Oh, maybe not; does LAST_INSERT_ID() work per connection?
But I'm not even clear on how that would help me. Let's see:

- User 1 does the UPDATE. id was 0, id+1 is 1, id is now set to 1.
- User 2 does the UPDATE. is is now 2.
- User 1 does the SELECT and gets 2.
- User 2 does the SELECT and gets 2.

Nope, no good. Or am I missing something?

What if I did:

UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID() FROM sequence;

- User 1 does the UPDATE. id was 0, id+1 is 1, id is now set to 1.
- User 2 does the UPDATE. is is now 2.
- User 1 does the SELECT and gets 1, the last ID he inserted.
- User 2 does the SELECT and gets 2.

I guess I'm going to have to play with this some more. I _know_ I saw
a tip on the web about doing just what I'm trying to accomplish but I
can't find it now. :-(

Roland Roberts

2004-05-18, 2:33 pm

-----BEGIN PGP SIGNED MESSAGE-----
[color=darkred]

[...]

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT id FROM sequence;

cn> But the problem is that that's _two_ statements. If I code that in
cn> MySQLTcl, there's a chance that two users are going to get the same
cn> ID, no? Oh, maybe not; does LAST_INSERT_ID() work per connection?
cn> But I'm not even clear on how that would help me. Let's see:

cn> - User 1 does the UPDATE. id was 0, id+1 is 1, id is now set to 1.
cn> - User 2 does the UPDATE. is is now 2.
cn> - User 1 does the SELECT and gets 2.
cn> - User 2 does the SELECT and gets 2.

cn> Nope, no good. Or am I missing something?

[...]

Does MySQL have "select for update" to allow locking? Does it have
some other locking semantics? Does it have transactions that will you
update and select? You really want these in a transaction:

BEGIN TRANSACTION
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT id FROM sequence;
END

roland
- --
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 6818 Madeline Court
roland@astrofoto.org Brooklyn, NY 11220

-----BEGIN PGP SIGNATURE-----
Version: 2.6.3ia
Charset: noconv
Comment: Processed by Mailcrypt 3.5.4, an Emacs/PGP interface

iQCVAwUBQKpIuOoW38lmvDvNAQEoxwP+LpZOL3ch
Pp9bHAaecFOuPYYD4ax6V6VD
AWZZ4WlMZYrRFawdYQyB3kTAxga9iJWu6PqA/bqdc3NHVcNg6T7pZ5ozrW737UWx
pUVHqVTUkjMc5TA27ntlbvFD/oAKtFEg1FbWK/PoKBDv+uXA3qVSFjW2ZaxsFQxA
K+psrXVvcjs=
=5z6G
-----END PGP SIGNATURE-----

tunity5@yahoo.com

2004-05-18, 3:43 pm

cnelson@nycap.rr.com (Chris Nelson) wrote in message news:<9245c0ba.0405180554.430f8e4b@posting.google.com>...

> But the problem is that that's _two_ statements. If I code that in
> MySQLTcl, there's a chance that two users are going to get the same
> ID, no? Oh, maybe not; does LAST_INSERT_ID() work per connection?
> But I'm not even clear on how that would help me. Let's see:

What version of mysql are you using and which engine? Assuming that
it supports transactions (e.g., innodb), you can wrap your atomic
logic in a transaction:

BEGIN TRANSACTION;
UPDATE ...
SELECT ...
COMMIT;

Within a transaction block, you can issue as many statements as you
need with the mytclodbc api calls. (I don't have the reference handy
but you may even be able to issue the whole thing in a single
mytclodbc call.) In any case, you get the same transactional, atomic
consistency.

You also need to turn off the auto-commit feature for this.

Sponsored Links