On Tue, 2004-03-09 at 07:05, Sagara Wijetunga wrote: > Hi all > > Is it possible to span a database transaction across > multiple CGI scripts? That is, start transaction and > lock some records in one CGI script and update and > commit in another CGI script.
No - unless you can find a way to make *sure* that the second CGI script gets the *same* physical connection to the database (and even then the connection would have to have been persistent, which isn't the case by default in CGI scripts.) The usual way that you solve this problem is with optimistic locking. This can implemented in various ways - for example create a "rows locked" table, and mark the table/row(s) that you want to modify in that table. Then make sure that everyone else plays nice and checks this table before updating any rows in the real table(s). You should also include a timeout for orphan locks (locks that are held by a client that has gone away). For MS-SQL or Sybase databases you can also use a TIMESTAMP column, and use the value from that column to check and see if the row has been updated between the time you fetched the data and the time the update is performed. If the data has changed you can abort your own changes, or make appropriate changes to your update request. Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.peppler.org/ Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.peppler.org/resume.html
