-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
On Tue, 20 Nov 2001, Heikki Tuuri wrote:
> When someone buys a particular type of good, I would like to lock those
> rows from the table, but since the quantity will change, I don't even want
> other users to see these rows until I'm finished. When I lock the entire
> table, I can do this. When I use "for update", or "lock in share mode",
> other threads cannot update, but they can still see these rows.
>
> Is there a way to lock ROWS, so they're not VISIBLE until the first thread
> is done with them? I would like to be able to use other rows though from
> other threads.
>
> .......
>
> Perhaps you want to query the table with
>
> SELECT ... LOCK IN SHARE MODE ?
Nope, doesn't do what I want.
I'll try to draw a table, with some example data:
THREAD A THREAD B
1. mysql> begin work;
Query OK, 0 rows
affected (0.00 sec)
2. mysql> begin work;
Query OK, 0 rows
affected (0.00 sec)
3. mysql> select * from teszt
where id < 10 lock in share
mode;
+----+------+
| id | tmp |
+----+------+
| 1 | 1408 |
| 2 | 1409 |
| 2 | 1407 |
| 3 | 1404 |
| 4 | 1403 |
| 5 | 1402 |
| 5 | 1409 |
| 6 | 1401 |
| 7 | 1400 |
| 8 | 1399 |
| 9 | 1397 |
+----+------+
11 rows in set (0.03 sec)
4. mysql> select * from teszt where
id < 5 lock in share mode;
+----+------+
| id | tmp |
+----+------+
| 1 | 1408 |
| 2 | 1409 |
| 2 | 1407 |
| 3 | 1404 |
| 4 | 1403 |
+----+------+
5 rows in set (0.03 sec)
This happens now. What I would like is #4 to wait, until thread A
finishes (commits OR rolls back). "id" is a key, buy not a unique key,
since there can be several rows of the same value there. If it were a
unique key, my wish would come true. :)
If, instead of "lock in share mode", I try "for update", then it works,
but not how I'd like it. Then #4 will wait, but it will wait even if the
query is "select * from teszt where id > 15 for update".
So it's basically like locking the entire table, and I cannot work with
other data.
It WORKS this way, I was just trying to speed it up.
If this particular case cannot be solved otherwise, I'm still happy,
because I could get rid of 90% of the lock tables. That's still better
than nothing. :)
Except... see next mail, on a different topic. :)
Thanks,
Attila
...........................................................................
In nature there are neither rewards nor punishments; there are
consequences. -Robert Green Ingersoll, lawyer and orator (1833-1899)
- ---
Public key: http://civ.hu/attila.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE7+iboDeyfLhmXxQwRAuMEAJ9WDB2EgkxDqlZ8ZQEl+aBGtLlyZACeISaS
oKldGkk/nJIRGkmyXdtDwC8=
=swnC
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
