MySQL UDF

[Jat421]

Hi, I am using the mysql udf to connect to remote mysql server. Wanted to check on the best way to accomplisying the following.

I have a table with two columns. One is called id that is auto increment and the other one is link_no. So, basically the idea is to get a link_no from the table and delete it at the same time.

So the number is only used by one user and not get picked up by other user. It will be a really busy database and many users will be grabing the link_no. So my question is what are the chances of users grabbing the same number? and what is the best way to minimize the delay time between a link_no picked up and deleted? Thanks for any help!

This is what I have so far. I am little confused about the delete statement

$i = _Query($sql, "select link_no from temp_tbl limit 1")
      _Query($sql, "delete from temp_tbl where..
with $i
$a = .Fields("link_no").value

   MsgBox(0,'test',$a)

_MySQLEnd($sql)

Please let me know if you need any other info. Thanks!

Edited June 4, 2012 by Jat421

[hannes08]

From this website (Link) I found the following:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT foo FROM bar WHERE wee = 'yahoo!';
DELETE FROM bar WHERE wee = 'yahoo!';
COMMIT;

Maybe this will help you.

[Jat421]

Thanks, this might help me. Can I send all these SQL statements in one query like this?..when I try it gives me a error "Return $oConnectionobj.execute ($sQuery)^ ERROR"

$i = _Query($sql, "
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT foo FROM bar WHERE wee = 'yahoo!';
DELETE FROM bar WHERE wee = 'yahoo!';
COMMIT")

Edited June 4, 2012 by Jat421

[Jat421]

Also I am looking at LOCK TABLE that might work for me. Any suggestions on that?

[jchd]

What you seem to need is simply this SQL:

delete from mytable where link_no = 123456;

This will atomically delete the row(s) having link_no = 123456

Of course you'll have to provide the value you need in place of 123456.

This single statement can't be interrupted and is atomic, which means that it either succeeds or fail altogether as part of one operation. Hence no two users can have issues with that behavior, either by reading (SELECT) or writing (UPDATE or DELETE).

If you need a more complex condition, just expose it so we can provide actual help.

[Jat421]

Thanks Jchd for helping!...so when a select statement and the the delete statement is sent is it possible for other user to send select statement within those two queries?

And if a second user sends a select query before the delete statement of the first user he/she will get the same number?

Sorry if I am getting this totally the wrong way.

Edited June 4, 2012 by Jat421

[jchd]

As a general rule, SQL engines use transactions to handle concurrency. Any single SQL statement is atomic (google that) which means you can't "see" an intermediate state thru another connection: either the single statement is not started (or fails) or it's completed.

Now you see that this isn't enough in very simple cases: using your favorite ATM is a common case. The ATM "reserves" the requested amount until the notes are delivered. No other operation on your card/account can take place in between, or that would violate the invariant "ATM can't deliver more than the account limit + allowed credit".

SQL offer a simple construct for that: explicit transactions. Transaction (a BEGIN statement, then a series of other SQL statements, then either a COMMIT or a ROLLBACK) make the statements between BEGIN and COMMIT (or ROLLBACK) atomic: either fully completed or not at all. During a transaction, other connections can't see what's going on, they acces the previous state only. After COMMIT or ROLLBACK has completed, other connections see the new state only.

As I understand it, your table has two columns; ID of type identity (an autoincrement integer) and a link_no integer supplied by you.

If you need to delete the row(s) having link_no = <some value>, just use a DELETE as in my previous post.

If link_no contains unique values, then you're garanteed to delete at most 1 row. Else SQL will faithfully delete any row satisfying the condition, inside an implicit transaction.

If you do:

SELECT link_no from mytable where <which condition should go here?>

and put that into a AutoIt/php/python/ruby/whatever variable you cal $var, then

DELETE from mytable where link_no = <content of $var>

you're doing exactly what the single-line DELETE does, but you're no inside an atomic transaction. So if after your SELECT, another connection (user or thread or program) runs the same SELECT, the first connection then proceeds with the DELETE, then the second transaction tries to DELETE as well (using the same link_no value!), it won't delete anything since the row(s) are already deleted.

In this scheme, you just don't need to first SELECT then DELETE. Use the WHERE clause of the DELETE statement to specify which row(s) the DELETE should remove.

My question would be: how do users (or programs or thread) choose the link_no value?

[hannes08]

If he does a

SELECT from table LIMIT 1;

he'll recieve the first line of the query.

So it's kind of a "random" or a first-in-first-out situation he wants to create.

The problem here is that he doesn't want to rely on the "id" column of the table.

Another thought would be to add a third column that shows whether it's already "marked for deletion".

1. Insert a user specific value:

UPDATE temp_tbl SET mark = '" & @UserName & "' LIMIT 1

2. Run the Select

SELECT link_no FROM temp_tbl WHERE mark = '" & @UserName & "' ( LIMIT 1 )

3. Delete the row

DELETE FROM temp_tbl WHERE mark = '" & @UserName & "'

What you think about that?

[jchd]

Ah, is that a really kind of random single unique pick?

If so, that's almost as easy:

DELETE from mytable where id = (select id from mytable limit 1)

[hannes08]

Ah, is that a really kind of random single unique pick?

If so, that's almost as easy:

DELETE from mytable where id = (select id from mytable limit 1)

I can't see where he will get the value from link_no from that statement.

[jchd]

So you interpret the OP problem and link_no semantics as a token distribution, randomly selected from a table?

It was very unclear to me what the aim was, so this may be the reason.

If so the table design isn't the right choice: remove the ID (serves no purpose) and make link_no the primary key.

Then in the probable case where the picked link_no will be used for significant time by the user who picked it, your proposal works fine.

If users are permitted to interrupt the program before the final delete occurs and if a given user may not pick more than one token at a time, one may have to clean up posible orphan rows from time to time. For instance by performing an

update temp_tbl set mark = null where mark = QuotingAndEscapingFunction(@UserName)

before the update.

However how to properly deal with orphan tokens should be dictated by the actual OP context.

[hannes08]

jchd, I'm not 100% sure if I got the OP request right as well.

we'll see what the OP really wants to achieve if he answers to this.

[Jat421]

My question would be: how do users (or programs or thread) choose the link_no value?

When a user clicks a button the first link_no in the table will be given to that user. It doesn't matter what number they get as long as two people don't get that same link_no. So that link_no should be deleted as soon as it's displayed on a users screen.

And I think you are correct I don't need the column id auto increament. I can very well make the link_no primary. Yes, you are correct that link_no is uploaded by me in the database.

I think hannes suggestion of adding the third column just might work. So, by doing that the first SQL statement marks the first row with a username and no other user can get that number and only that user will get it and then it can be delete after. Great idea!!. Thanks!!!

Edited June 5, 2012 by Jat421

[ProgAndy]

hannes08 has the right idea, but there should be more tests while assigning the number and LIMIT 1 in an UPDATE clause is AFAIK not alwayys working.

Table: link_no (PK), user_id

- assign link_no:

UPDATE linktable SET user_id='THISUSER' WHERE link_no = (SELECT link_no FROM linktable WHERE user_id = '' LIMIT 1)

- get link_no:

SELECT link_no FROM linktable WHERE user_id = 'THISUSER'

- delete link_no:

DELETE FROM linktable WHERE user_id = 'THISUSER'

or: DELETE FROM linktable WHERE link_no = link_no_previously_stored_in_script

Edited June 5, 2012 by ProgAndy

[Jat421]

Thanks that looks like better way of doing it. Thanks you again all for your help!!