473,699 Members | 2,475 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Regarding Update/select Query

Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .

Jul 11 '06 #1
5 8331
First of all a select query on an innodb table should not take 10-20 seconds
to complete. There's something wrong somewhere. You can turn the slow query
log on to find out what is holding you up. You can ready about the slow
query and how to turn it on at www.mysql.com

Secondly, innodb uses what is called multi versioning which enables every
query to see their own version of the data it is trying to access (where for
read and/or update). Locking is rare and if it is needed then innodb uses
row level locking which means as long as you're not updating what you're
reading, performance should not effected (no wait).

Hope this helps.
-s
On 7/11/06 07:09, in article
11************* *********@b28g2 00...legr oups.com,
"pa************ @gmail.com" <pa************ @gmail.comwrote :
Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .
Jul 11 '06 #2
nope ! in some case seniario SELECT query takes major part of tables
...
and it takes more than 20 seconds . in b/w if we fire UPDATE qry
it also takes time to complete .
so i want to confirm that still innodb table has row level locking,
will SELECT query reads snapshot of DB without acquiring a read lock on
it and let UPDATE statment
do what row it want to update . or it will wait till SELECT qry
completes.
i dont know much about MySQL internal how it handle this queries . plz
help me !


Shawn Hamzee wrote:
First of all a select query on an innodb table should not take 10-20 seconds
to complete. There's something wrong somewhere. You can turn the slow query
log on to find out what is holding you up. You can ready about the slow
query and how to turn it on at www.mysql.com

Secondly, innodb uses what is called multi versioning which enables every
query to see their own version of the data it is trying to access (where for
read and/or update). Locking is rare and if it is needed then innodb uses
row level locking which means as long as you're not updating what you're
reading, performance should not effected (no wait).

Hope this helps.
-s
On 7/11/06 07:09, in article
11************* *********@b28g2 00...legr oups.com,
"pa************ @gmail.com" <pa************ @gmail.comwrote :
Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .
Jul 12 '06 #3
Yes, innodb utilizes row level locking. And also don't forget about multi
versioning like I said in the previous post.

Your updates like I said before should not take that long to return. You can
use background updating (I think you have to include some modifiers in your
sql statements) which will do the work behind the scenes; however, it
returns control to the calling program.
On 7/12/06 03:01, in article
11************* *********@m73g2 00...legr oups.com,
"pa************ @gmail.com" <pa************ @gmail.comwrote :
nope ! in some case seniario SELECT query takes major part of tables
..

and it takes more than 20 seconds . in b/w if we fire UPDATE qry
it also takes time to complete .
so i want to confirm that still innodb table has row level locking,
will SELECT query reads snapshot of DB without acquiring a read lock on
it and let UPDATE statment
do what row it want to update . or it will wait till SELECT qry
completes.
i dont know much about MySQL internal how it handle this queries . plz
help me !


Shawn Hamzee wrote:
>First of all a select query on an innodb table should not take 10-20 seconds
to complete. There's something wrong somewhere. You can turn the slow query
log on to find out what is holding you up. You can ready about the slow
query and how to turn it on at www.mysql.com

Secondly, innodb uses what is called multi versioning which enables every
query to see their own version of the data it is trying to access (where for
read and/or update). Locking is rare and if it is needed then innodb uses
row level locking which means as long as you're not updating what you're
reading, performance should not effected (no wait).

Hope this helps.
-s
On 7/11/06 07:09, in article
11************* *********@b28g2 00...legr oups.com,
"pa*********** *@gmail.com" <pa************ @gmail.comwrote :
>>Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .
Jul 12 '06 #4
yes my query is multitable update query in normal senario it takes less
than 1 second .
since it update only one row from each table which binded with foreign
keys ..

some times it takes time up to 40 sec which is not feasible now i want
to know whether
the select queries are takes lock or not .. these queires create temp.
table by select query .. some times it contains lacs of row in some
where conditions ..

one more thing these create temp. table from select takes lock on it
or not !

and what is background update can u till me ..

thanx for replying my qry.

Jul 12 '06 #5
It's a way for mysql to allow return of control to the calling program while
it's processing the data. I am not that good to have memorized all the
details; however, you can read about it on MySQL.com site. I remember that
you just add a modifier to the update.
On 7/12/06 12:09, in article
11************* *********@p79g2 00...legr oups.com,
"pa************ @gmail.com" <pa************ @gmail.comwrote :
yes my query is multitable update query in normal senario it takes less
than 1 second .
since it update only one row from each table which binded with foreign
keys ..

some times it takes time up to 40 sec which is not feasible now i want
to know whether
the select queries are takes lock or not .. these queires create temp.
table by select query .. some times it contains lacs of row in some
where conditions ..

one more thing these create temp. table from select takes lock on it
or not !

and what is background update can u till me ..

thanx for replying my qry.
Jul 12 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1433
by: Bill Agee | last post by:
I have a Select query which contains a field called AMWnum (long) which represents a sequence # of sorts. If I want to copy a row of information and paste it on the bottom, all is OK. I have another query which finds the max AMWnum in the table and then adds 1. What I would like to do is automatically update the copied record's AMWnum field with the value found in the MAX_AVMnum query.
1
1746
by: septen | last post by:
Hi, I have a select query of 12 tables. The SQL code is as follows: SELECT OptChannelRangeAve.ChannelRangeMin, OptChannelRangeAve.ChannelRangeMax, OptChannelRangeAve.SampleAveraging, OptChannelADcard.ChannelRangeAveID, OptChannelChannel.ADcardID, OptChannelNameChannel.ChannelEveryID, OptChannelNameChannel.ChannelID, OptChannelNameChannel.NameID, OptChannelNameChannel.SignAnalID,
1
2627
by: Greg Strong | last post by:
Hello All, Any reason why a select query that runs will not run as an update query? What I've done is created a select query that runs successfully. The query has several custom functions to create data. When I attempt to run the query as an update query I receive an error message which reads:
3
1966
by: phonl | last post by:
I am a vb6 ADO developer looking at vb.net 2005 and ADO2.net. I used the vb.net 2005 data wizard to bind some controls to a database. Now I want to run a select query and have the bound controls reflect the change from the query. How would I do that? The wizard created a TableAdapter, BindingSource, and DataSet. Somehow I need to run feed them a select query to update the data.
3
9116
by: RAG2007 | last post by:
I'm using the QueryDef and Execute method to update a record in my MySQL backend. Problem: When the Passthrough update query is defined using QueryDef, it becomes a select query, and I cannot use the Execute Method. Instance: The following code is defined as a query called "AddCost" UPDATE tblinitiative SET tblinitiative.Estimate = " & Estimate & ", tblinitiative.FinalCost = " & FinalCost & " WHERE (((tblinitiative.InitID)=" & InitID &...
4
1525
by: msalman | last post by:
Hey guys, I'm trying to update some columns in a table using another table's data but there seems to be some problem due to join among the two tables. Here is my query Update DMM_SalesReporting..sapis_test SET Claims_Qty = ISNULL(Claims_Qty, 0) + CONVERT(varchar(30), b.BaseUOMQuantity), Claims_Dollars = ISNULL(Claims_Dollars, 0) + b.BaseUOMAmount, Update_DateTime = getdate(), MM_Batch_Num = convert(varchar(30),...
5
11056
by: MARIEDB2 | last post by:
Hello, I am struggling to build a query on a DB2 db wich does an update for multiple fields which are the result of a select query with where clause (using multiple tables in the condition). E.g. UPDATE TABLE_HDR SET HDRFIELD1 = 'XX' WHERE EXIST (SELECT HDRFIELD1 FROM TABLE_HDR,TABLE_1A,TABLE_1B WHERE FIELD_1A= FIELD_1B AND HDRFIELD1=FIELD1A)
4
2116
by: fran7 | last post by:
Hi, I have a size field where the input is in quotes like 20" x 20" It writes to the database as I see the correct output in the webpage but when I open the record to update it it removes the quotes and I just get the first digets. I have tried imagesize= Replace(imagesize, "'", "''") but that only replaces ' with " I have looked around but cannot see the right solution. If anyone has the answer that would be great. Might it be that the...
0
1461
by: GEETHA LAKSHMI | last post by:
I want to use where conditions in both update and select query cmd1.CommandText = "Update AttRegister set Present= (Select count(Working) from Attendance where Attendance.Working='P' and Attendance.EmpCode=" & row!Empcode & " and Attendance.AttDate between #" & Convert.ToDateTime(CmbFromDate.Text.Substring(6, 4) + "/" + CmbFromDate.Text.Substring(3, 2) + "/" + CmbFromDate.Text.Substring(0, 2)) & "# and #" &...
0
8704
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9054
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8895
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7781
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6546
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4637
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3071
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2362
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.