MySQL LOCK and UNLOCK

[Anon]

Hi,

I am interesting in knowing what happens to requests to a table that is LOCKed? Does MySQL have some sort of queue, so it can handle incoming requests? or are there some kind of timeout involvoed?

Any remarks on this would be great.

Thanks in advance.

Stefan

[badman]

By default mySql 'locks' the table while its processing a query moment this query is done table is unlocked to other queries.This is as far it goes though.

This means that table is locked for one single query at a time.if you wish to process multiple queries and dont want table to be modified by any other query then you have to LOCK table by using LOCK query(see docs for exact implementation)
and then fire your multiple queries and then UNLOCK using UNLOCK query.

this will ensure that at any given time only one person/source is modifying the table.
This lock is of different types too eg read, write etc.

hope you find this useful!

If you wish to lock a table for

[Anon]

Yes, thanks 🙂

But what I am wondering about is, if I (pseudo):

LOCK myTable, myTable2;
insert into myTable VALUES(2,2,2);
update myTable2 set name = "Zeev";
UNLOCK myTable, myTable2;

We agree the two tables are locked for the processing of both queries, but what if some other mySQL thread (some other instantiation) would make a

insert into myTable2 VALUES("Jack");

at the same moment as the two tables are LOCKed? The last query surely has no effect as long the the two tables are locked, but how does mySQL handle the "locked out" query? Does is simply ignore it, so it will never have effect? Does it make sure that "Jack" is inserted when the two tables have been unlocked again?

Thanks

Cheers,
Stefan

[badman]

sorry, havent tried doing this!
am looking into it and just in case you find something on it, please mail me at [e-mail]

cheers!