Bug #52354 for SQL-Statement: LOCK TABELES ....

Wed Dec 02 09:05:51 2009 DetlefPilzecker [...] web.de - Ticket created

Subject:

LOCK TABELES ....

"LOCK TABELES ...." is an important statement to lock the tables (files in my case, I use SQL::Statement with DBD::CSV) for the next queries. Is it possible to set a flock on those files? Or is it already implemented but not documented? If not implemented jet I ask for it! :-) Thanks!

Wed Dec 02 15:31:49 2009 REHSACK [...] cpan.org - Correspondence added

There're no now features planned to be added in 1.xx tree. I want to create a rewritten engine with 2.00 - and therefore locking tables, indexes and other neat features shall be added. Maybe you might take a look to the documentation of DBD::CSV and DBD::File - locking is implemented there and should work easily during driver opening: my $dbh = DBI->connect ("dbi:CSV:", "", "", { f_lock => 2, # lock tables exclusive on open }) or die $DBI::errstr;

Wed Dec 02 15:31:50 2009 The RT System itself - Status changed from 'new' to 'open'

Wed Dec 02 15:31:50 2009 REHSACK [...] cpan.org - Status changed from 'open' to 'stalled'

Wed Dec 02 19:03:18 2009 DetlefPilzecker [...] web.de - Correspondence added

Show quoted text

> my $dbh = DBI->connect ("dbi:CSV:", "", "", { > f_lock => 2, # lock tables exclusive on open > }) or die $DBI::errstr;

Where did you get this info from? I looked into the documentation of all related main modules and found it nowhere. Do you have a link?

Wed Dec 02 19:03:18 2009 The RT System itself - Status changed from 'stalled' to 'open'

Thu Dec 03 07:16:26 2009 REHSACK [...] cpan.org - Correspondence added

I got this information from looking into the source. As Co-Maintainer I should do from time to time, even if Merijn is doing the most of the work (99%+), I'll try to keep up-to-date.

Thu Dec 03 07:16:27 2009 REHSACK [...] cpan.org - Status changed from 'open' to 'stalled'

Thu Dec 03 11:15:08 2009 HMBRAND [...] cpan.org - Correspondence added

Subject:

LOCK TABELS ....

the locking mechanism, even though it looks like being available in the code, is not documented anywhere. Not in DBI, not in DBD::File, not in DBD::CSV and not in SQL::Statement, and in none of these source packages I can find the word f_lock at all! It is obvious that since this feature seems to be available (for a longer time already), none of the modules seems to use it, nor document how it could be used. I do like the f_lock name. I'll have a second (and probably third, fourth and fifth) look at it later and see if it is generalized enough to document and implement correctly in DBD::File (where it belongs).

Thu Dec 03 11:15:09 2009 The RT System itself - Status changed from 'stalled' to 'open'

Thu Dec 03 12:47:09 2009 REHSACK [...] cpan.org - Correspondence added

You're right - I was mislead by $locking/$lockMode - and was to quick at all (happens sometimes).

Thu Dec 03 12:47:10 2009 REHSACK [...] cpan.org - Status changed from 'open' to 'stalled'

Thu Dec 03 15:08:32 2009 HMBRAND [...] cpan.org - Correspondence added

Subject:

LOCK TABELS ....

Added f_lock support to DBD::File and committed to DBI. Nothing more we can do from here.

Thu Dec 03 15:08:33 2009 The RT System itself - Status changed from 'stalled' to 'open'

Thu Dec 03 15:35:26 2009 DetlefPilzecker [...] web.de - Correspondence added

On Do. 03. Dez. 2009, 15:08:32, HMBRAND wrote: Show quoted text

> Added f_lock support to DBD::File and committed to DBI. > Nothing more we can do from here.

Do I understand it right, if I add "f_lock => 2" to the connect attributes, all files opened during the this process will be flocked until the program exists? You still plan to write a LOCK TABLES/UNLOCK TABLES for version 2 ? How long does the program wait (sleep) and try again to get a flock over an already flocked file by other instance of the program until it warns/exits?

Fri Dec 04 04:03:46 2009 REHSACK [...] cpan.org - Correspondence added

On Thu Dec 03 15:35:26 2009, DetlefPilzecker@web.de wrote: Show quoted text

> On Do. 03. Dez. 2009, 15:08:32, HMBRAND wrote:

> > Added f_lock support to DBD::File and committed to DBI. > > Nothing more we can do from here.

> > Do I understand it right, if I add "f_lock => 2" to the connect > attributes, all files opened during the this process will be flocked > until the program exists?

No - the files opened are locked until they are closed. This typically happens when you finish the statement (e.g. $sth->finish()) or the statement is implicitly finished (see DBI documentation, when that happens). Show quoted text

> You still plan to write a LOCK TABLES/UNLOCK TABLES for version 2 ?

Yes, and many more - e.g. index-support, strong type binding and several table types (for example to write a proxy table which is able to do a sub-request to a PostgreSQL database). Show quoted text

> How long does the program wait (sleep) and try again to get a flock > over an already flocked file by other instance of the program until it > warns/exits?

Forever - unless you create an alarm(). Maybe Merijn could be persuaded to implement flock($file, LOCK_EX | LOCK_NB ), too.

Sat Dec 05 08:18:02 2009 REHSACK [...] cpan.org - Correspondence added

Patched in current DBD::File in svn. Will be delivered with next release of DBI.

Sat Dec 05 08:18:04 2009 REHSACK [...] cpan.org - Status changed from 'open' to 'patched'

Thu May 06 17:57:40 2010 REHSACK [...] cpan.org - Correspondence added

Meanwhile DBI 1.611 is out which should solve this RT. Can you confirm that it fits your needs or can you specify in more details what could be done else?

Thu May 06 17:57:40 2010 The RT System itself - Status changed from 'patched' to 'open'

Thu May 06 17:58:21 2010 REHSACK [...] cpan.org - Status changed from 'open' to 'patched'

Tue Jul 27 16:35:34 2010 REHSACK [...] cpan.org - Correspondence added

No issue reported on this, so I assume it works fine. Thanks for your contribution.

Tue Jul 27 16:35:35 2010 The RT System itself - Status changed from 'patched' to 'open'

Tue Jul 27 16:35:35 2010 REHSACK [...] cpan.org - Status changed from 'open' to 'resolved'