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]
locking issues
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB locking issues
Hi, I'm running quite a large database - mostly inserts (4-5 million rows a day) and an occasional select to generate some report based on the inserted data which works very well. Once a month I need to generate a larger number of reports. The SELECT queries are optimized quite well but some of the reports require to group a large number of records together which could take up to 5 minutes, but that is OK for me. The problem is that when more than a certain number (between 4 and 8 - but I can find any stable pattern) of SELECT queries are working concurrently InnoDB starts to lock the threads that insert data and in a very short time MySQL uses all available connections. I couldn't find any reasons why InnoDB is locking INSERT threads which try to insert in tables different then the ones that the reports are SELECT-ing from. The InnoDB monitor shows a lot of transactions similar to this ---TRANSACTION 1 4271824648, ACTIVE 51 sec, process no 12904, OS thread id 712286614 setting auto-inc lock mysql tables in use 1, locked 1 LOCK WAIT 1 lock struct(s), heap size 320 MySQL thread id 485399224, query id 3713824274 69.59.185.156 websitepulse9 update INSERT INTO slogs8 VALUES (NULL,24801,'OK','0.00','62.40','62.56','62.82','N',Now(),0.479633*1000) --- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `websitepulse/slogs8` trx id 1 4271824648 lock mode AUTO-INC waiting Normally there are less than 200 running threads (1-2 active) but to avoid hitting the connections limit due to this locking problem I had to increase the maximum connections number to 600 and respectively had to decrease the size of the per-thread memory buffers which slows down the selects additionaly. I'll probably use a second server to replicate the database and run the reports from there but I wanted to see if somebody might had a different suggestion. Here is the servers info MySQL: 4.0.21-standard Official MySQL RPM OS : Red Hat Enterprise Linux ES 3 Memory : 4 GB DELL RAM Processor : Dual 3.06 GHz Intel Xeon RAID Configuration : RAID 1 146 GB SCSI Here is my /etc/my.cnf file [mysqld] port=3306 skip-name-resolve log-bin=/var/lib/mysql/mysql log-slow-queries=/var/lib/mysql/slow.queries.log socket=/var/lib/mysql/mysql.sock myisam-recover=BACKUP,FORCE set-variable = max_connect_errors=10 innodb_data_home_dir = innodb_data_file_path =/var/lib/mysql/innodbfile:100M:autoextend innodb_log_group_home_dir = /var/log/innologs innodb_log_arch_dir = /var/log/innologs set-variable = innodb_buffer_pool_size=1700M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable = key_buffer_size=500M set-variable = read_buffer_size=500K set-variable = read_rnd_buffer_size=1200K set-variable = sort_buffer_size=1M set-variable = thread_cache=256 set-variable = thread_concurrency=8 set-variable = thread_stack=126976 set-variable = myisam_sort_buffer_size=64M set-variable = max_connections=600 set-variable = table_cache=1 set-variable = wait_timeout=2000 Any suggestions are welcome. -- Mark J. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innoDB alter table and locking issues
Hello List, Recently I needed to alter a large innodb table (~200k rows) to add a column on a live server. The alter table ran fine on the development server with close to the same number of rows in the table so I didn't anticipate too much trouble. The list archive and the documentation indicate that alter table has some inneficenties - like rebuilding the entire table, indexes and all and that the table would be locked as read only while the alter table command was executing -- and the the alter table was likely to take A LONG TIME. (one poor guy posted that he gave up on altering his table after it was still running for 24 hours!) What we found is that the process list quickly filled up with SELECT statements that were 'Waiting for table' -- The connections maxed out (at 500) since the webservers were still sending queries, which I figured would be okay since they are reads, but apparantly these queries were locked out. I found myself locked out , unable to issue more commands and had to kill mysqld. Since all ALTER TABLE commands will probably exhibit suimilar locking - I am weary of even creating a new table with another name with the new structure, populating it with data from the table I want to alter, dropping the old table and renaming the new one back to the old one's name. So I'm wondering a few things if people can chime in: - does ALTER TABLE really just lock read only or does it just flat out LOCK? - might there be some configuration setting that influences this locking? - what strategies do people use to alter live busy tables ? Thanks for any and all input! Jeff Kolber ps: we are using: mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686) -- 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]
locking issues
Hi Group, 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. 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,. Thanks for help Hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
locking issues
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:
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
This message and any attachment has been virus checked by
Pfizer Corporate Information Technology, Sandwich.
-
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
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
More locking issues
Hi, I'm new to MySQL. I see you all talking about locking databases etc... but isn't that the point of having a database server? So you don't have to worry about record locks? I'm designing the backend for a popular site where a user's visit will result in multiple read/write operations on a database. Is record locking something I definitely need to do? Thanks, Jeff - 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: More locking issues
Hi, If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 6:57 PM Subject: More locking issues Hi, I'm new to MySQL. I see you all talking about locking databases etc... but isn't that the point of having a database server? So you don't have to worry about record locks? I'm designing the backend for a popular site where a user's visit will result in multiple read/write operations on a database. Is record locking something I definitely need to do? Thanks, Jeff - 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 - 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: More locking issues
Gelu Gogancea wrote: If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Can I ask if this is in fact accurate? As I understand it, MySQL does a fairly good job of locking the tables, that's part of the performance problem of MyISAM vs. InnoDB table types with many updates running. And with InnoDB, you get a Repeatable Read isolation level by default which 'feels like' getting a read lock. What did you mean that it doesn't lock tables? SQL -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: More locking issues
Hi, Is not the same caseJeff didn't said anything about TRANSACTIONS. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael T. Babcock [EMAIL PROTECTED] To: Gelu Gogancea [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Jeff Snoxell [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 8:44 PM Subject: Re: More locking issues Gelu Gogancea wrote: If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Can I ask if this is in fact accurate? As I understand it, MySQL does a fairly good job of locking the tables, that's part of the performance problem of MyISAM vs. InnoDB table types with many updates running. And with InnoDB, you get a Repeatable Read isolation level by default which 'feels like' getting a read lock. What did you mean that it doesn't lock tables? SQL -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: More locking issues
From MySQL Reference Manual: ... 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. * If you are using a table handler 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 below 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. ... That is the way I work with MySQL. In almost all cases I do not need to lock tables. I only use it when I am updating more than one tables, because of chances of getting data integrity broken. Hope I could help. []s Anderson Pereira Ataides Em Qui 12 Dez 2002 16:09, you wrote: Hi, If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 6:57 PM Subject: More locking issues Hi, I'm new to MySQL. I see you all talking about locking databases etc... but isn't that the point of having a database server? So you don't have to worry about record locks? I'm designing the backend for a popular site where a user's visit will result in multiple read/write operations on a database. Is record locking something I definitely need to do? Thanks, Jeff - 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 - 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 --- - 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
locking issues
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:
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
This message and any attachment has been virus checked by
Pfizer Corporate Information Technology, Sandwich.
-
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
my sql database and locking issues(URGENT)
Hi, 1. Does mysql inherently implement locking implicitly? 2. The following is the extract from mysql documentation: In MySQL Version 3.23.7 and above, you can insert rows into MyISAM tables at the same time other threads are reading from the table. Note that currently this only works if there are no holes after deleted rows in the table at the time the insert is made. ACTUALLY,VERSION 3.23.7 IS NOT RELEASED.ARE THESE THE EXPECTED FEATURES IN THE COMING VERSION? Regards, Ravi Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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: my sql database and locking issues(URGENT)
2. The following is the extract from mysql documentation: In MySQL Version 3.23.7 and above, you can insert rows into MyISAM tables at the same time other threads are reading from the table. Note that currently this only works if there are no holes after deleted rows in the table at the time the insert is made. ACTUALLY,VERSION 3.23.7 IS NOT RELEASED.ARE THESE THE EXPECTED FEATURES IN THE COMING VERSION? 3.23.7 is long gone! we know on 3.23.38 Regards, Ravi Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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 - 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
