CODEWITHSUNDEEP
mysqlsql
Giovani Grifante
Giovani Grifante

Reputation: 472

Function to increment and return a value (MySQL)

Lets suppose I've a row with ID equals to 1 in table t.

If I run the following sql, I get a result equals to 1 (when column c is 0):

SELECT NEXT_ID(1)

But, if run this, I get 1 as a result, instead of 0 (as there is no row with ID = 2 in table t):

SELECT NEXT_ID(2)

NEXT_ID function:

CREATE FUNCTION NEXT_ID(id INT)
RETURNS VARCHAR(15)
 BEGIN
  DECLARE counter BIGINT DEFAULT 0;
  UPDATE t SET c = (@counter := c +1) WHERE ID = id;
  return @counter;
 END;

My intent here is to create a counter that increment a value as an atomic operation. So, why do I get a value greater than 0 on NEXT_ID(2)? It seems like the counter variable is been stored in the session...

Is this safe to use in a multithreaded application?

Upvotes: 1

Views: 3878

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562250

If you DECLARE counter then you should NOT use @counter to refer to the variable. In a MySQL stored function, declared variables don't have the @ sigil. Variables with the @ sigil are user-defined variables. @counter is a different variable than counter, even though they have the same spelling.

Is it safe to do in a multi-threaded app? Certainly if the threads are incrementing different rows by using different id values, they will not conflict (assuming id is a unique key of the t table).

Even if multiple threads use the same id value, and therefore need to increment the same row in the t table, what will happen is that one will get there first, get a lock on the row, and increment it. The second thread will wait to get its own lock until the first thread commits its transaction. Then the second thread will proceed, and will then see the incremented value of c.

So it's safe, but it will not allow a high rate of throughput. Threads that are in contention for the same id will have to queue up and wait for the current holder of the lock to commit their transaction. If you're expecting multiple threads to do their work in parallel, you'll find this becomes a bottleneck.

This is why AUTO_INCREMENT exists, because it locks the counter briefly to generate a new value, but it releases the lock immediately, not waiting for the caller's transaction to finish. This allows concurrent threads to keep working and not wait for one another.

You can't simulate the behavior of AUTO_INCREMENT with UPDATE operations, which are necessarily in transactions.

Re your comments:

Sorry, I forgot that := doesn't work with local declared variables. I tested it and I found two alternatives:

Alternative 1: don't bother to declare a local variable, just use the user-defined variable.

CREATE FUNCTION NEXT_ID(id INT)
RETURNS VARCHAR(15)
READS SQL DATA
 BEGIN
  UPDATE t SET c = (@counter := c +1) WHERE ID = id;
  RETURN @counter;
 END

Alternative 2: use a local variable, but set the LAST_INSERT_ID() to the incremented value. Then SET the counter local variable to that value.

CREATE FUNCTION NEXT_ID(id INT)
RETURNS VARCHAR(15)
READS SQL DATA
 BEGIN
  DECLARE counter BIGINT DEFAULT 0;
  UPDATE t SET c = LAST_INSERT_ID(c +1) WHERE ID = id;
  SET counter = LAST_INSERT_ID();
  RETURN counter;
 END;

I tested both alternatives and they work.

Upvotes: 1

Related Questions