Re: transactions -- whole table locked from reads too?
On Fri, Feb 16, 2001 at 01:05:51PM -0500, Scott McCool wrote: > > My guess is that this is what was happening. I'm not sure of any > way to confirm it though, how do I ensure that my select() is > happening from a different database page then the insert? Realistically, you can't. It's a guessing game. Your data may or may not all be on the same page. It's no different than wondering which block on the filesystem a particular group of MySQL records were stored. They may or may not be on the same block. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transactions -- whole table locked from reads too?
My guess is that this is what was happening. I'm not sure of any way to confirm it though, how do I ensure that my select() is happening from a different database page then the insert? -Scott -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Friday, February 16, 2001 10:38 AM To: [EMAIL PROTECTED] Subject: Re: transactions -- whole table locked from reads too? Scott, did you try to do the insert and select on the same database page? In BDB there is page level locking used, and the behavior you describe sounds like that the insert has placed a page level lock on the page where you try to do the select. Then it is correct behavior of the database that your select has to wait until the commit which releases the locks placed by the insert. A possible solution would be to use 'dirty read', i.e., non-locking read in select, but I do not know if BDB provides such. My own Innobase table handler allows you to read and insert data concurrently with no interference from locks, because Innobase does in a select a consistent non-locking read, in the style of Oracle. But MySQL/Innobase is not available yet, it will come out in MySQL 3.23.3x in a few days (see my posting a couple of hours ago). Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions -- whole table locked from reads too?
Scott, did you try to do the insert and select on the same database page? In BDB there is page level locking used, and the behavior you describe sounds like that the insert has placed a page level lock on the page where you try to do the select. Then it is correct behavior of the database that your select has to wait until the commit which releases the locks placed by the insert. A possible solution would be to use 'dirty read', i.e., non-locking read in select, but I do not know if BDB provides such. My own Innobase table handler allows you to read and insert data concurrently with no interference from locks, because Innobase does in a select a consistent non-locking read, in the style of Oracle. But MySQL/Innobase is not available yet, it will come out in MySQL 3.23.3x in a few days (see my posting a couple of hours ago). Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
transactions -- whole table locked from reads too?
If I start a transaction (set autocommit=0 in the command line utility), then do an "insert" in one session, then open a second session and try to do a select on that table, before the first session has committed, the select just hangs. Once I commit in the first session, my select proceeds fine. Is there some reason the whole table seems to be locked from reads just because someone has an uncommitted transaction? The table type is BDB, it's running on Solaris 7/SPARC. Thanks for any help you can offer! -Scott - Scott McCool [EMAIL PROTECTED] (703)847-3303x2051 -
