Re: InnoDB Row Lock test (A query maybe?)
Hi Shawn,
Thanks for taking time to respond to this.
[...]Usually the database sets and releases locks like that in
response to a series of statements on the order of sub-seconds, not
for the several seconds to minutes that may be required of an
application-level lock).
Actually this is a bit different: On one side of the database is a
telephony application which requires that certain information in the table
does not change for the period of time that a call is active - and the
only way such information would change is if it were modified from the web
interface. Because within the [multithreaded] telephony system there are
application level locks that enforce concurrency in this respect - testing
a mutex lock here would tell whether an account is available or
unavailable quite easily - without having to attempt to lock a particular
set of rows in the DB.
The issue comes in ensuring that the web application does not modify that
information in the table while a call is in progress. To me it looked
like a DB level solution of some sort would be the best.
I have a similar situation (another user may already be editing a
particular record. only one user can edit a record at any one time)
in one of my web-based applications. I created a new field to hold
the ID of the application user that has the exclusive privileges on
that record. That way the application deals with application-level
logic (no more than one editor at a time) and I use db-level
transactions to set and unset the editor field as appropriate.
The record is locked for the shortest length of time and you can
easily and quickly check the value of the field so that your users
can know immediately if they have rights to edit the record or not.
That's an interesting way to do it, actually - it'll be abit of work but I
think its not bad at all. I was just curious about whether you could
test for a lock in InnoDB - it'd save alot of trouble.
Opening a transaction on one page request and closing it on another
(as when the results are submitted) will be VERY difficult for you
to manage as transactions are connection-specific and unless you are
using a pooled or global connection variable,
Actually, as I explained its abit different - its not between page
requests that I want to open / close transactions but rather to make sure
the web application users do not modify table information while a call is
in progress (i.e while the telephony application is holding the lock to
a given user's information).
you will be creating and destroying connections rather frequently.
Actually not, the web application architecture is abit as below:
[webserver]-{ENV}-[Small CGI Program]-{TCP/IP}-[Daemon]===[MySQL]
^^^
I.e there's a pool of persistent connections to the MySQL database that
are maintained and reused / shared.
It's generally not a good idea to put a DB-level lock (TABLE lock or
open transaction) on a record to enforce an application-level rule,
especially in the mostly-stateless, asynchronous world of web-based
development.
I see. What are some of the reasons why this would be so?
Thanks!
Rgds,
Gerald.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Row Lock test (A query maybe?)
Hi, I'm writing an application that uses InnoDB tables to provide transactional integrity. The front-end is a web-based interface. I'd like to know - is there a way one can issue a query to test whether a particular set of rows (or row) has already been locked by another session - rather than the default action of waiting for quite long for the lock to be granted. The idea is so that a person using the front-end may know whether the information they are attempting to access is unavailable at the time. There will be situations where rows will be locked exclusively for prolonged times and I wouldn't want the web application users to be subjected to these lengthy delays but rather be told to try later. Regards, Gerald. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using both embedded and 'normal' server in a client
On Thu, 16 Oct 2003, Serge Cohen wrote: [...] Now I'd like to use the libmysqld, so that I can have a an embedded server, still this same application should be able to connect to a DB server (over internet). From what I've read and tried, the libmysqld library does not provide the functionality to connect to a 'standalone' server (over IP or socket). If I understand your question correctly, you're asking this: Can your application that has been linked with libmysqld still connect to an external mysql server? If that's so, my answer is yes. The only difference between an application linked with limysqlclient and libmysqld is that one is capable of being a server as well as a client (the one linked with libmysqld) and one is purely a client (the one linked with limysqlclient) The limitation is that that an application linked with libmysqld cannot receive TCP/IP connections to it - you have to implement this yourself. I have the impression that to have the two functions (embedded server and connection to standalone server) a single application should link to both libmysqlclient and libmysqld. No: You only need to link one of the libraries depending on what your needs are. If you do not need to have a server *within* your application then you should link libmysqlclient only. But if you want to have a server in your application but have the same application connect to an external server (over TCP/IP) then you should link libmysqld. The problem I see there is that as these two libraries have exactly the same API IMHO, I see it as the beautiful thing about them. I have no precise clue on how to link both libraries and know precisely which implementation of a library function I'm calling. You only need to know how to link either, you don't need both, as I explained above. So far the only solution that I kind of see is to modify the sources of one of the library so that it has a different API than the other (like modifying the mysql_ prefix of the API functions to something else - - -mysqld_ for example-). But this solution does not seems very clean to me, unless there is a standard way to do it from within the configure/build process of mysql (which I've not found). Definately not very clean, just read through the docs for MySQL 4.0.15 again and again and again (and once more) then you'll begin to link things up - ok maybe you won't need to read them that many times (you may be much smarter than I am). I'd be very grateful to anyone who can provide : 1. a way to have both functionalities using a single API/library. Explained. 2. a better idea to avoid the API clash while linking to both library. Explained (you don't need to link both libraries) 3. a 'clean' (or at least automatic) way to modify one of the API. Or any insight on these matters. You don't need to 'clean' either of the API's as explained: In my opinion the folks at MySQL are about the MOST brilliant guys I've come across! I do greatly appreciate the work they do! Cheers, Gerald. -- Kampala, Uganda East Africa. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scalability question
Hi, Imagine I had a table with 50,000,000 rows, would it improve search speeds if I split it up into smaller tables of 10,000,000 rows each? This means if I had to search for a record, I'd have to query the 5 tables one after the other. I'd greatly appreciate any help. Gerald. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scalability question
On Thu, 25 Sep 2003 [EMAIL PROTECTED] wrote: [...] Which is likely to be slower. Wow! [...] Just give it lots of Ram so that it can cache its indexes in mamory, and it will perform very well. Thanks for taking time to answer this. Cheers, Gerald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory and C API...
Hi,
Could you post your definitions for 'dbase' and 'err'...
Gerald.
On Tue, 23 Sep 2003, Lars Wenderoth wrote:
Hello there!
I have a problem with a little C program i am writing.. It uses
SSL-encrypted connections to a special server. This server needs
information from a MySQL database. The SSL-encryption is running fine.
The MySQL code runs fine, if the SSL code is not there.. Since i need
both at the same time in the same program, i have a problem! ;-)
if(mysql_init(dbase) == NULL) err = 1;
else
{
if(mysql_real_connect(dbase,host,user,pw,
db,0,NULL,0) == NULL)
err = 2;
}
if(err)
{
printf(\nError connecting to database\n);
if (err == 1) printf(Insufficient memory!\n);
exit(0);
}
I always receive the message Insufficient memory. The rest of the
program unfortunately needs huge amounts of memory, but when it is
running, there are still several hundreds of megabyte free memory..
I would like to know, how much memory the dbase structure needs.. I
suppose, that it will not be much..
Any other idea?
Thanks,
--
Lars Wenderoth [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory and C API...
Hi, I don't see anything wrong with that. If I was in your shoes I'd make sure I don't have any buffer overflows anywhere between the definitions and where you use the variable - these are notorious to only cause segmentation faults when there's no more memory to silently consume (e.g variable to overwrite etc...) Its absurd that you should be getting an 'insufficient memory' error - in your case when you call mysql_init(), you are not asking it to allocate memory for you - you are giving it an object to initialize. In my opinion if the problem was insufficient memory, the program would fail at the definitions themselves (i.e being unable to allocate space for the MYSQL object itself). Anyone? Any other ideas? Gerald. On Tue, 23 Sep 2003, Lars Wenderoth wrote: Hi! Sure! They are: int err = 0; MYSQL dbase; It might be helpful to say, that I'm running linux... Lars Am Die, 2003-09-23 um 14.47 schrieb Begumisa Gerald M.: Hi, Could you post your definitions for 'dbase' and 'err'... Gerald. -- Lars Wenderoth [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
