Regarding Update/select Query

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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 .

Re: Regarding Update/select Query

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

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.

On 7/11/06 07:09, in article,

Quoted text here. Click to load it

Re: Regarding Update/select Query

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

i dont know much about MySQL internal how it handle this queries . plz
help me !

Shawn Hamzee wrote:
Quoted text here. Click to load it

Re: Regarding Update/select Query

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,

Quoted text here. Click to load it

Re: Regarding Update/select Query

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.

Re: Regarding Update/select Query

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 site. I remember that
you just add a modifier to the update.

On 7/12/06 12:09, in article,

Quoted text here. Click to load it

Site Timeline