On Tuesday 20 August 2002 01:26 pm, Randy Johnson wrote: > I am confused. (innodb table type)
I'm really not so sure about the 'lock in share mode' thing, but to the best of my knowledge if you do a SET TRANSACTION_ISOLATION_LEVEL=SERIALIZABLE and then start a transaction where you read data from a row and then update it, there is an absolute guarantee (if the database properly honors the isolation level) that no two transactions can act in such a fashion that either one interferes with the other. In practical terms that means that the same code run from client 2 will block as soon as it attempts the read until transaction started in client 1 is 100% complete. Now, there may be more efficient ways to get this result. InnoDB uses multi-versioning and that has some subtle effects on transactions and concurrency. A larger question however is this, why do you care about reading the old balance? If you aren't going to use it to calculate the new one, then its irrelevant... In other words the scenario you outline reduces (as far as the db is concerned) to just update table set balance=100 and since that is an atomic operation it requires no transaction. In fact in theory ACID never requires a transaction for any operation involving only one single row. For instance if you were incrementing the balance by 100 it would STILL be an atomic operation update table set balance=balance+100 It is in fact only when you get to multi-row or multi-table situations where transactions are required. Consider again your example, since no matter what order the 2 operations are performed in the resut is the same (balance is 100) there is no point in caring what sequence occurs, esp since script 1 cannot care if script 2 ever runs or not, and vice versa (or else they'd be one script...). You can satisfy yourself that the same is true for increment, decrement, or ANY other single-row scenario that can possibly be invented. This is in fact a theorem of transactions... Why then were transactions invented? Suppose you had THREE rows you needed to update with a single update statement update table set balance = balance=1 where id =1 or id = 2 or id = 3 NOW you might need a transaction, because it might be a really bad idea for script 2 to come along and do select balance from table where id =1 or id = 2 or id = 3 and end up with the incremented balance for row 1, and the unincremented balances for rows 2 and 3, which is quite possible. In that case running the 1st query in a transaction would in fact be quite necessary. Now you know what keeps db design guys up late at night.... > > Client 1. > starts transaction > selects balance from table where id=1 for update > update table set balance=100 > #At this point from what i have read the balance could be selected by > anther user. > > committ > > > > I have read that a select balance from table where id=1 lock in share mode > will wait for the committ statement, but client 2 would be wanting to > update the balance the same way client 1 does sO i do not see how client 2 > could use the lock in share mode because the script is the same for client > 2 as it is in client one. > > so how would i ensure that client 2 waits for client 1 to committ before > processing their select and update? > > > > Randy > > sql,quary > > > > --------------------------------------------------------------------- > 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
