hi, Selecting a non-existent row won't acquire any locks that prevents inserts from happening. One way to accomplish what you want is to create a separate insert lock table consisting of a table name and a lock counter. Add a row for each table that you want to have these insert locks on, and before performing any inserts, either update the corresponding row in the insert lock table or select it for an update.
joe On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: > Hi Chris, > > Thanks for the response and the suggestions. Doesn't SERIALIZABLE level just add > 'LOCK IN SHARE > MODE' to your SELECTs, but other than that works just like the default REPEATABLE > READ level? I've > tried by example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but the > results were the > same. > > Is there any way to make the second session block when both it and the first one are > 'locking' > non-existence of a row? > > Thanks in advance, > > Alex > > -----Original Message----- > From: Chris Nolan [mailto:[EMAIL PROTECTED] > Sent: Friday, January 16, 2004 4:55 PM > To: Zeltser, Alex > Cc: [EMAIL PROTECTED] > Subject: Re: InnoDB locking 'non-existence' of a row > > > Hi Alex! > > On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: > > Hi, > > > > I wanted to take advantage of the InnoDB 'gap' locking to lock > > 'non-existence' of a row, the way the manual recommends. I tried to > > do this by using 'select ... for update', using the 'mysql' client > > from two separate sessions as shown below: > > > > Session 1: > > > set AUTOCOMMIT=0; > > > begin; > > > select * from T where A = 'NOT_THERE' for update; > > > > Session 2: > > > set AUTOCOMMIT=0; > > > begin; > > > select * from T where A = 'NOT_THERE' for update; > > > > What I'd hoped to see was the 'select' statement in Session 2 block > > until either a commit or a rollback was performed in Session 1. > > Unfortunately, it didn't work that way. The 'select's in both > > sessions returned right away, and it was only the subsequent > > 'insert's, 'update's and 'delete's that blocked. I can understand the > > rationale behind this behavior, but unfortunately it doesn't help me > > with my problem. I'd like to be able to reliably check for existence > > of a record from two concurrent sessions and have the 'select' in the > > 'second' session block until the first session is either committed or > > rolled back. Is there a way to accomplish this somehow? > > > InnoDB's next-key locking is a bit different to this - it ensures that phantom rows > do not appear. > This is good for application writers and for MySQL itself, as phantom rows appearing > would break > MySQL's replication. > > Basically, InnoDB will place locks on the various index structures involved in your > query around the > rows that have been returned bt a SELECT ... FOR UPDATE. As your selects return > nothing, InnoDB > doesn't find any index sections to place any locks on. > > Perhaps you should look at using the SERIALIZABLE level of transaction isolation. > > Regards, > > Chris > > > I know I can just try to insert the record and check for duplicates, > > but is there a way to accomplish it with 'select's? > > > > Thanks in advance, > > > > Alex Zeltser > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Joe Shear <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
