blocked table while running select

Archived from the Xataface Users forum.

Taralea — Fri Jun 01, 2012 6:05 am

Hello everybody,

i want to use “LOCK IN SHARE MODE” for each select query which is generated by Xataface itself.
For example: I have a table with a fields.ini and nothing else, so i don’t care about the query.
Is there a way to tell Xataface to put “LOCK IN SHARE MODE” after each query or should i write the tablequery by myself? When i write it by myself, what happens with the query if i use filter or limit?
It’s a little confusing, hope you could help me.

thanks,
Taralea

---

shannah — Fri Jun 01, 2012 10:28 am

Interesting request. Why do you want to use lock in share mode? What are you trying to achieve?

---

Taralea — Mon Jun 04, 2012 5:23 am

I use views in a database for Xataface and want to prevent a blocking of the whole database while running a large select. It’s necessary for the user, to be able to insert or update data at the same time the select is running. Xataface is used only to show the data not to change them. I use lock in share mode in all functions and selects in the database but i have to call the views with it too.

---

shannah — Mon Jun 04, 2012 11:28 am

I think that LOCK IN SHARE MODE will have the opposite effect of what you want. If you perform a SELECT with LOCK IN SHARE MODE, it will prevent any other sessions from modifying any of the rows that you found in the select until you either commit your transaction, or your session ends (i.e. the end of the HTTP request).
http://dev.mysql.com/doc/refman/5.0/en/ … reads.html

The default behavior (without using LOCK IN SHARE MODE) sounds like what you want. It will perform the select without blocking any other sessions from performing their work.

-Steve

---

Taralea — Mon Jun 18, 2012 4:58 am

Sorry for the delay,
you are right. I have read a wrong article about that. Thanks so much for your help.

Taralea