Selon Harald Fuchs <[EMAIL PROTECTED]>: > In article <[EMAIL PROTECTED]>, > Julien ALLANOS <[EMAIL PROTECTED]> writes: > > > Thanks, I've already read these pages. > > > Here is a test example I've done: > > > 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; > > 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; > > 3/ User A: START TRANSACTION; > > 4/ User B: START TRANSACTION; > > 5/ User A: SELECT * FROM MyTable LIMIT = 1; > > I got a one-row result. > > 6/ User B: SELECT * FROM MyTable LIMIT = 1; > > I got the same one-row result, and I need to get the next one instead, > because > > this one is already being treated by user A! > > > I really want to have a "SELECT FOR DELETE" thing: as soon as a row is > read, it > > is deleted. Is this possible? > > Not quite, but close. You need a SELECT.. FOR UPDATE. Then in step 6/ > above the B connection will get blocked. Now you can delete the row > in the A connection and commit. Then B will get unblocked and return > the "next" row. > > Just two things: > 1. Use "ORDER BY pkey LIMIT 1" instead of just "LIMIT 1". You might > get away without the ORDER, but the behavior is undefined. > 2. Remember the result of the "SELECT.. FRO UPDATE", do the DELETE > and COMMIT immediately, and process the row values afterwards if > possible. This will cut down the time B gets blocked. >
Well, I've tried the following scenario: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got the same one-row result again! B isn't blocked and gets the same result than A, though A hasn't commited/rollbacked yet. Furthermore, I don't want B to be blocked: I want B to get the "next" result while A is processing data and deleting the current row. Any solution to this please? Thanks. -- Julien ALLANOS Silicomp-AQL ************************************************************ The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * ************************************************************ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
