Re: Locking question

On Thu, Oct 27, 2016 at 1:37 AM, Frank Millman <frank(at)chagford(dot)com> wrote:

> As mentioned previously, I am trying to avoid using PostgreSQL-specific
> techniques, as I need to support sqlite3 and SQL Server as well.

The SERIALIZABLE transaction isolation level is portable. It it
part of the SQL standard (and has been since the beginning), and is
supported by just about every database product, including SQLite
and SQL Server. (In fact, you have to go well out of your way for
SQLite transactions *not* to be SERIALIZABLE --
https://www.sqlite.org/isolation.html )

> For costing purposes, I want to run a FIFO system. This means I have to
> maintain separate entries for each receipt of stock, and allocate any sales
> of stock against the receipts ‘oldest first’.

The two ways of doing this which spring to mind are window
functions (supported by PostgreSQL and SQL Server, but not SQLite)
and cursors (supported by most database products, including the
three you mention).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company