Re: locking issues
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html PaginaDeSpud wrote: i'm using myisam. Is there any tip i should know before to migrate this table to innodb ? Thanks ! - Original Message --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: locking issues
Hello. What table engine do you use for your tables? InnoDB usually is the best choice if you have lots of concurrent updates and inserts. PaginaDeSpud [EMAIL PROTECTED] wrote: I'm getting locking issues due to tables very often updated/insert. It's splitted into two tables, one has the more updated data and the another has the more static data. I'm using queries with JOIN, would it be better for performance to use two queries instead of using JOIN? Thanks. Ivan L. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: locking issues
i'm using myisam. Is there any tip i should know before to migrate this table to innodb ? Thanks ! - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 29, 2005 12:35 PM Subject: Re: locking issues Hello. What table engine do you use for your tables? InnoDB usually is the best choice if you have lots of concurrent updates and inserts. PaginaDeSpud [EMAIL PROTECTED] wrote: I'm getting locking issues due to tables very often updated/insert. It's splitted into two tables, one has the more updated data and the another has the more static data. I'm using queries with JOIN, would it be better for performance to use two queries instead of using JOIN? Thanks. Ivan L. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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]
Re: locking issues
Excuse me for late response. When I do it from the mysql prompt it works fine but trough the API (I use DAC for MySQL from microolap, maybe the bug is in there software!!) it doesn't work fine. I have try it on several versions: 4.1.1, 5.0.0 and 4.0.0. Thank you for your help. If you think it is not a database bug or a API bug I contact microolap for more support. hans At 19:38 8-3-04, you wrote: Hans van Dalen [EMAIL PROTECTED] wrote: A question about locking. In my code (delphi but that doesn't much matter) I fire ad-hoc queries (select). Before the query i fire a locking query (read) for that particular tables. Something like this: lock tables tab1 a read, tab2 b read (execute) select * from tab1 a, tab2 b where. (execute) In this case I get an error: tab1 is not locked using LOCK TABLES. Works fine for me: mysql lock tables tab1 a read, tab2 b read; Query OK, 0 rows affected (0.00 sec) mysql select * from tab1 a, tab2 b where a.id=b.id; +--+--+ | id | id | +--+--+ |2 |2 | +--+--+ 1 row in set (0.00 sec) What exactly versions do you use? If I change the locking query to: lock tables tab1 read, tab2 b read The first times I didn't get any error. But after some times of running my app the error: a is not locked using LOCK TABLES occur!?!? Ok finally I use the statement: lock tables tab1 read, tab1 a read, tab2 b read en everything works fine. The strangest thing about this is that sometimes it doesn't go wrong (locking using the lock statement with only the table aliases), other times it goes. When I execute the locking and the select query from my SQL explorer (borland) nothing is going wrong (makes no sense whatever you lock). It makes no difference I use version 4.0/4.1/5.0 (on suse linux). Anybody who has seen this before? Okay the workaround is simple: don't use table aliases or use the above showed locking statement, but it makes me angry something like this errors are raised on unexpected moments in my production environment,. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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]
Re: locking issues
Hans van Dalen [EMAIL PROTECTED] wrote: A question about locking. In my code (delphi but that doesn't much matter) I fire ad-hoc queries (select). Before the query i fire a locking query (read) for that particular tables. Something like this: lock tables tab1 a read, tab2 b read (execute) select * from tab1 a, tab2 b where. (execute) In this case I get an error: tab1 is not locked using LOCK TABLES. Works fine for me: mysql lock tables tab1 a read, tab2 b read; Query OK, 0 rows affected (0.00 sec) mysql select * from tab1 a, tab2 b where a.id=b.id; +--+--+ | id | id | +--+--+ |2 |2 | +--+--+ 1 row in set (0.00 sec) What exactly versions do you use? If I change the locking query to: lock tables tab1 read, tab2 b read The first times I didn't get any error. But after some times of running my app the error: a is not locked using LOCK TABLES occur!?!? Ok finally I use the statement: lock tables tab1 read, tab1 a read, tab2 b read en everything works fine. The strangest thing about this is that sometimes it doesn't go wrong (locking using the lock statement with only the table aliases), other times it goes. When I execute the locking and the select query from my SQL explorer (borland) nothing is going wrong (makes no sense whatever you lock). It makes no difference I use version 4.0/4.1/5.0 (on suse linux). Anybody who has seen this before? Okay the workaround is simple: don't use table aliases or use the above showed locking statement, but it makes me angry something like this errors are raised on unexpected moments in my production environment,. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: locking issues
On Thursday 12 December 2002 14:06, Greg_Cope at sandwich dot pfizer dot com
wrote:
I may be confused here but it would appear that when you issue a LOCK
TABLES tbl_name { READ|WRITE }; You cannot read from another unlocked table
in the same connection eg:
mysql LOCK TABLES users read;
Query OK, 0 rows affected (0.00 sec)
mysql select * from Logger limit 1;
ERROR 1100: Table 'Logger' was not locked with LOCK TABLES
I've read section 6.7.2 of the Manual:
http://www.mysql.com/documentation/mysql/bychapter/index.html#LOCK_TABLES
This is on 3.23.43.
I assume this is expected behaviour? Although at the moment it seems a
little odd that this thread cannot read from another (unlocked) table
whilst it has a lock.
Does anyone have a solution that I may be missing? We are locking tables
to do a fake transaction so that we can update a number of tables without
interference from other threads.
If you read the above section of the manual you can see:
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!
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
-
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
Re: locking issues
At 19:52 + 12/9/02, [EMAIL PROTECTED] wrote:
Hi All,
I may be confused here but it would appear that when you issue a LOCK TABLES
tbl_name { READ|WRITE }; You cannot read from another unlocked table in the
same connection eg:
You're supposed to lock all the tables that you'll need until you
issue the UNLOCK TABLES statement. Not just some of them.
mysql LOCK TABLES users read;
Query OK, 0 rows affected (0.00 sec)
mysql select * from Logger limit 1;
ERROR 1100: Table 'Logger' was not locked with LOCK TABLES
I've read section 6.7.2 of the Manual:
http://www.mysql.com/documentation/mysql/bychapter/index.html#LOCK_TABLES
This is on 3.23.43.
I assume this is expected behaviour? Although at the moment it seems a
little odd that this thread cannot read from another (unlocked) table whilst
it has a lock.
Does anyone have a solution that I may be missing? We are locking tables to
do a fake transaction so that we can update a number of tables without
interference from other threads.
Thanks for any replies.
Greg Cope
JITC
-
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
