using perl to lock tables without locking myself

sdyates has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: using perl to lock tables without locking myself by gellyfish (Monsignor) on Mar 12, 2002 at 21:03 UTC
You do want to use your SQL server to do this - there is always the possibility that some other process not under your control will attempt to use your database. You don't mention what database server it is that you are using but does it support the: `LOCK TABLE foo IN EXCLUSIVE MODE` [download] syntax ? /J\	[reply] [d/l]
Re: Re: using perl to lock tables without locking myself by sdyates (Scribe) on Mar 13, 2002 at 02:51 UTC
It is amazing. When you look at code over and over, quite often the most obvious mistake is missed. Seeing my question online alog with your answer forced me to look at it again. Lock tables support read will not work if one wants to write. I saw it as, lock the table read access to all but me... oh well... I still need to read over the exclusive mode... mysql.com is nto the best... I think I need a mysql for dummies book. Anyhows thanks for the help. I know some of you think I am pushing it by aasking a mysql question, but hey, I did not know whether the problem was with how I was phrasing the mysql code in perl, or with my mysql sql statements... Remember, there is no such thing as a dumb question, just dumn users--sorry.	[reply]
Re: using perl to lock tables without locking myself by webadept (Pilgrim) on Mar 12, 2002 at 22:05 UTC
Jellyfish is on the money here. Any locking should alwasy be done by the database engine in control of the table. You might want to checkout This Page to see if an answer is in there for you. Glenn H.	[reply]
Re: using perl to lock tables without locking myself by rdfield (Priest) on Mar 13, 2002 at 10:58 UTC
I've been DBA'ing for quite a while now (12 years) on some very big systems (1000s of users, TBs of data) and I've never come across an situation where 'LOCK TABLE' was a 'good thing'. In fact in most situations it is the most significant obstacle to both performance and scalability. rdfield	[reply]
BB Re: using perl to lock tables without locking myself by knobunc (Pilgrim) on Mar 13, 2002 at 17:48 UTC
Rather than lock the whole table you might be able to use SELECT ... FOR UPDATE to only lock the rows you are about to change. Although that is usually only necessary if you need to select data then munge it before writing it back to the table. I assume that the above code is not complete (I am guessing at that because you have stray parentheses and your prepare statement is preparing the string "SQLCommand" not the variable $SQLCommand). Since all you are doing is an update which should not have any concurrency problems. Note that SELECT ... FOR UPDATE requires a halfway competent DB, namely one that supports transactions. -ben	[reply]