Kim,
I use WRITE locks extensively with MySQL 4 with a large distributed
application.
Your example logs indicate that your test application obtained a READ lock,
which will not block other select requests from accessing the table. Only a
WRITE lock will cause subsequent select requests to be held until the lock
is released.
If your application's break point actually closes the database connection,
then the WRITE lock is released, and subsequent selects will be processed.
In my application, I have several dozen systems all working on data that it
reads in chunks from the MySQL backend. In my application, I do the
following:
LOCK TABLES the_big_backend_table WRITE
(select my data then flag the selected rows as pending)
UNLOCK TABLES
Over the last year or so, this has worked perfectly. It keeps all the other
systems from selecting duplicate data, just as you are trying to do, I
assume. If you are doing a JOIN with a table you are trying to lock, you
must also specific read locks for each table in your JOIN, if I recall
correctly, or else the lock will not hold. I have to do this in another
location:
LOCK TABLES bigtable AS a WRITE, smalltable AS b READ
Again, all of this locking and releasing should have some degree of
concurrence. You cannot take a lock, close the database connection, and
expect the lock to hold. Your application requesting the lock must keep the
session active. Open two command line instances of MySQL to the same
database. In one, instruct the database to lock your first table for WRITE
access. Then, go to your second instance, and try to select, without
shutting down the first or closing it. You will find that you cannot select
data from the table as the first MySQL client has locked it for writing.
Release the lock with UNLOCK TABLES on the first client, then you will see
your second client has noticed the lock release, and has read the data.
-- R
Hello
I try in my program to make a lock.
I try to prevent another session to read and write from the whole table
I tried both
LOCK TABLES pstockx WRITE
and
LOCK TABLES pstockx READ
but no success.
I put in a breakpoint in my program to be sure the lock are held
and then I try to make a select from another session.
It dont lock me up :(
What am I doing wrong ,,is it some settings on server ??
8128 Query LOCK TABLES pstockx READ
031211 0:22:058133 Connect [EMAIL PROTECTED] on elprint
8133 Query SET SQL_BIG_SELECTS=1
8133 Query select amount from pstockx where
productID =
82846
8133 Query EXPLAIN select amount from pstockx
where pro
ductID = 1
031211 0:23:068128 Query SHOW COLUMNS FROM pstockx
8128 Query SHOW INDEX FROM pstockx
8128 Query select amount from pstockx where
productID=8
2846
TIA
Kim G. Pedersen
macaos/elprint Development
+45 35373808
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
