visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Getting current "counter" and incrementing it too.
By: RQuadling U.S.A.  Date: 11/03/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
Hi.

First up, this is a multi-user environment.

I have about 50 users (all via browsers) entering form based info and I have an external script generating data too (from other DBs).

I want to be able to get a single integer from a table to be used as a reference number for "this" user and then add 1 to it, so the next user has the next number.

Several scripts will also be running which are going to be working unattended to also insert information into the system on a batch basis. The value returned is NOT used as an autonumber but does needs to be unique.

So, how do I get the current value AND then add 1 to it for the next user.

or

How do I add 1 to a value AND get the result so I can subtract 1 from it to use for "this" user.

Essentially an UPDATE and a SELECT in 1 go (or a SELECT and UPDATE in 1 go!).

Richard.
By: VGR Date: 11/03/2003 05:33:00 English  Type : Comment
well, this can be solved the classical way (two queries, not atomic then :D ) and made "atomic" (or more precisely a "critical section") by explicitly LOCKing the table(s), no ?

LOCK
perform operations
UNLOCK
By: RQuadling Date: 12/03/2003 20:04:00 English  Type : Comment
Locking. That's what I want.

What happens to other queries whilst the lock is in place?

Does it wait until the lock is cleared or does it generate an error?

And can I lock just 1 table?
By: VGR Date: 12/03/2003 20:09:00 English  Type : Answer
1) yes
2) they are blocked (READ and/or WRITE)
3) wait
4) yes (theoretically)

references :

2) "If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can read from or write to the table. Other threads are blocked. "

1) "If you are using a storage engine in MySQL that doesn't support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES in order to execute safely:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
mysql> UPDATE customer SET total_value=sum_from_previous_statement
-> WHERE customer_id=some_id;
mysql> UNLOCK TABLES;
"


that's from :

<A HREF="http://www.mysql.com/doc/en/LOCK_TABLES.html">http://www.mysql.com/doc/en/LOCK_TABLES.html</a>

6.7.2 LOCK TABLES/UNLOCK TABLES Syntax

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES

LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are automatically unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.

To use LOCK TABLES in MySQL 4.0.2 you need the global LOCK TABLES privilege and a SELECT privilege on the involved tables. In MySQL 3.23 you need to have SELECT, insert, DELETE and UPDATE privileges for the tables.

The main reasons to use LOCK TABLES are for emulating transactions or getting more speed when updating tables. This is explained in more detail later.

If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can read from or write to the table. Other threads are blocked.

The difference between READ LOCAL and READ is that READ LOCAL allows non-conflicting INSERT statements to execute while the lock is held. This can't however be used if you are going to manipulate the database files outside MySQL while you hold the lock.

When you use LOCK TABLES, you must lock all tables that you are going to use and you must use the same alias that you are going to use in your queries! If you are using a table multiple times in a query (with aliases), you must get a lock for each alias!

WRITE locks normally have higher priority than READ locks, to ensure that updates are processed as soon as possible. This means that if one thread obtains a READ lock and then another thread requests a WRITE lock, subsequent READ lock requests will wait until the WRITE thread has gotten the lock and released it. You can use LOW_PRIORITY WRITE locks to allow other threads to obtain READ locks while the thread is waiting for the WRITE lock. You should only use LOW_PRIORITY WRITE locks if you are sure that there will eventually be a time when no threads will have a READ lock.

LOCK TABLES works as follows:

Sort all tables to be locked in a internally defined order (from the user standpoint the order is undefined).
If a table is locked with a read and a write lock, put the write lock before the read lock.
Lock one table at a time until the thread gets all locks.
This policy ensures that table locking is deadlock free. There is however other things one needs to be aware of with this schema:

If you are using a LOW_PRIORITY WRITE lock for a table, this means only that MySQL will wait for this particlar lock until there is no threads that wants a READ lock. When the thread has got the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other threads will wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transactions safe tables.

You can safely kill a thread that is waiting for a table lock with KILL. See section 4.5.5 KILL Syntax.

Note that you should not lock any tables that you are using with INSERT DELAYED. This is because that in this case the INSERT is done by a separate thread.

Normally, you don't have to lock tables, as all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. There are a few cases when you would like to lock tables anyway:

If you are going to run many operations on a bunch of tables, it's much faster to lock the tables you are going to use. The downside is, of course, that no other thread can update a READ-locked table and no other thread can read a WRITE-locked table. The reason some things are faster under LOCK TABLES is that MySQL will not flush the key cache for the locked tables until UNLOCK TABLES is called (normally the key cache is flushed after each SQL statement). This speeds up inserting/updateing/deletes on MyISAM tables.
If you are using a storage engine in MySQL that doesn't support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES in order to execute safely:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
mysql> UPDATE customer SET total_value=sum_from_previous_statement
-> WHERE customer_id=some_id;
mysql> UNLOCK TABLES;

Without LOCK TABLES, there is a chance that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements.
By using incremental updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, you can avoid using LOCK TABLES in many cases.

You can also solve some cases by using the user-level lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See section 6.3.6.2 Miscellaneous Functions.

See section 5.3.1 How MySQL Locks Tables, for more information on locking policy.

You can lock all tables in all databases with read locks with the FLUSH TABLES WITH READ LOCK command. See section 4.5.3 FLUSH Syntax. This is very convenient way to get backups if you have a filesystem, like Veritas, that can take snapshots in time.

NOTE: LOCK TABLES is not transaction-safe and will automatically commit any active transactions before attempting to lock the tables.

User Comments
Posted by Lars Geisler on Friday October 18 2002, @12:51pm [Delete] [Edit]

Remember to lock ALL tables you need between LOCK TABLES and UNLOCK TABLES, including tables you just read from! otherwise you will get error 1100: table 'a' not locked in LOCKED TABLES
By: RQuadling Date: 12/03/2003 21:42:00 English  Type : Comment
Excellent!

Here's what I've got ...

...
$sQuery = 'LOCK TABLES control WRITE;';
$result = mysql_query($sQuery);
$sQuery = 'SELECT NextID FROM control WHERE id=1;';
$result = mysql_query($sQuery);
$row = mysql_fetch_array($result);
$i = $row[0];
mysql_free_result($result);
$sQuery = 'UPDATE control SET NextID=NextID+1;';
$result = mysql_query($sQuery);
$sQuery = 'UNLOCK TABLES;';
$result = mysql_query($sQuery);
...

This is the bit. The whole script is now running about 100 times. I've got the script running 10 times on my machine and no errors/conflicts.

So.

Thanks!

Richard.
By: VGR Date: 12/03/2003 22:47:00 English  Type : Comment
never used the mysql_free_result()
is it serious, Doc ? :D
By: RQuadling Date: 12/03/2003 22:58:00 English  Type : Comment
I always like to keep things clean. If you intend to re-use $result for another query, then clearing out the old values/memory/etc helps, though it will all be cleared out when the script ends.

Check php.net dox for this.

Richard.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 304.290060 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page