Bryce Lohr-2 wrote:
>
> In this case, the easiest thing to do would probably be to cause the DB
> to set an exclusive lock on the row with the counter at the time it
> reads it. Then, your application can decide if it needs to decrement the
> value, delete the row, or whatever, and do the appropriate write.
>
> Example in MySQL/InnoDB using SELECT ... FOR UPDATE
>
> There's certainly a way to achieve the same effect in PostgreSQL...
>
I agree with all that Bryce said in his posting.
For what it's worth, PostgreSQL supports the same syntax as MySQL in this
case:
SELECT ... FOR UPDATE
(Support for FOR UPDATE clause in other database brands:
Oracle: yes;
IBM DB2: yes;
FireBird/InterBase: yes, but syntax is FOR UPDATE WITH LOCK;
MS SQL Server: no, but the UPDLOCK hint is similar;
SQLite: no)
It _should_ also work to specify SERIALIZABLE isolation mode when you begin
your transaction. However, the implementation of SERIALIZABLE is
nonstandard in both PostgreSQL and MySQL/InnoDB; you can't get a true
serializable transaction as specified in the SQL standard.
You might already know this, but it bears repeating that MySQL's MyISAM
storage engine doesn't support transactions at all, so neither the FOR
UPDATE clause nor the transaction isolation level will have any effect.
Zend_Db has no special API methods for FOR UPDATE or transaction isolation
levels. You'll have to execute transaction commands as SQL statements and
bear in mind the database-specific behavior.
Fortunately, the FOR UPDATE clause appears at the end of a SELECT statement
in both MySQL and PostgreSQL. So if you create a Zend_Db_Select query,
here's how you can render it to its string equivalent and append the clause:
$select = $db->select()->from('tablename')->order('colname'); // for
example
$sql = $select->__toString() . ' FOR UPDATE'; // notice the extra space
inside the quotes
$stmt = $db->query($sql);
Note that this works only if you're executing SQL queries directly using the
database adapter's methods such as query() or fetchAll(). If you're using
Zend_Db_Table objects, it renders SELECT queries internally, so you don't
have an opportunity to append the FOR UPDATE clause in that case. You can't
specify this clause for queries run by Zend_Db_Table objects (except if
you're willing to hack the internals of this class, and also
Zend_Db_Table_Row).
Regards,
Bill Karwin
--
View this message in context:
http://www.nabble.com/simple-synchronization---transaction-question-tp18480201p18513715.html
Sent from the Zend Framework mailing list archive at Nabble.com.
