Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

Snorri Bergmann wrote:
>
> Ok. Here is an easy one. Only involves 2 tables (master and detail).
> This is of course very simlified and I have not checked the syntax :-)
>
> CREATE TABLE master (id INT PRIMARY KEY,
> name VARCHAR(31),
> address VARCHAR(21),
> amount DECIMAL(16,4) );
>
> CREATE INDEX x1 ON master (name);
> CREATE INDEX x2 ON master (address);
>
> CREATE TABLE detail (id INT REFERENCES master,
> line_no INT,
> item_no INT,
> descr VARCHAR(100),
> cost DECIMAL(10,4));
>
> ALTER TABLE detail PRIMARY KEY(id, line_no);
>
> Now what the app does is it allows users to select a record from the
> master table, get all child records from the detail table and allow the
> users to add/delete/modify those records as needed. After the user exits
> it updates the
> master table by selecting the sum of cost from the detail table. The sql
> statements involved are something like this: (assume everything is under
> transaction control)
>
> -- We need to lock the master table so nobody else will update it while
> we are.
>
> SELECT * FROM master WHERE id = ? FOR UPDATE;
>
> -- No need to lock the detail recs 'cause the master rec is locked.
>
> SELECT * FROM detail WHERE id = ? ORDER BY line_no;
>
> -- After the user exits we update all detail recs, probably by deleting
> them first, and then inserting whatever the user has typed in.
>
> -- Then we'll need to update the master table and release the lock.
>
> UPDATE master SET amount = (SELECT sum(cost) FROM detail WHERE id = ?)
> WHERE id = ?;
>
> COMMIT;
>
> If you have row level locking, the first select stmt will lock one row.
> As the primary key is 4 bytes you can potentially put 4-500 keys on any
> page (if you have 2K pages). In a page lock system (like Sybase and
> MSSQL) the probability of a user trying to access one of the ~499 key
> values another user has locked is pretty high.
>

That depends. If you have a small number of master records, then the probability of a lock collision is high, but then you *can* extend
the key to be 1K, since you don't need that much disk. If you have a large number of master records, then you can control the probability of a lock collision via a clustered index.

> Page lock workarounds could be:
>
> 1) Make the primary key >1Kb, so you would only have one key per page.
> (Disks are cheap, right?)
>
> 2) Use optimistic locking. (So users who have been typing in for hours
> get the message: Somebody else has modified this rec while you were
> working. Please try again).

Okay, but under row level locking in this case, nobody would be able to view the records in question while the other user spent hours and hours typing.

>
> 3) Swich to Informix :-)
>
> Take care,
> -Snorri
>
> --
> Snorri Bergmann | Mail: snorri_at_strengur.is
> Strengur Consulting Engineers | WWW: http://www.strengur.is/
> Armuli 7 | Phone: +354 550 9000 (9007 direct)
> 108 Reykjavik Iceland | Telefax: +354 550 9010
Received on Tue Nov 25 1997 - 00:00:00 CET