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? 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]
