Wednesday, April 19, 2006

current row version checking. uses for concurrency checking

MS SQL:

-- checks if row has changed since last read
CREATE TABLE Distributor
(
MemberId VARCHAR(20),
TotalSales NUMERIC(10,2),
ConcurrencyCheck TIMESTAMP -- not related to ANSI-SQL 92 timestamp
)


INSERT INTO Distributor(MemberId, TotalSales) SELECT 'Michael',1000
SELECT * FROM Distributor
UPDATE Distributor SET TotalSales = TotalSales + 7000
SELECT * FROM Distributor

Here's the result:

MemberId TotalSales ConcurrencyCheck
-------------------- ------------ ------------------
Michael 1000.00 0x0000000000007C3B

MemberId TotalSales ConcurrencyCheck
-------------------- ------------ ------------------
Michael 8000.00 0x0000000000007C3C



---------------------------------------------------------

PostgreSQL:

Note: there's a hidden field in each postgres table, xmin for example, xmin changes every time changes happen in the row

-- checks if row has changed since last read
CREATE TABLE Distributor
(
MemberId VARCHAR(20),
TotalSales NUMERIC(10,2)
);


INSERT INTO Distributor(MemberId, TotalSales) SELECT 'Michael',1000;
SELECT *, Distributor.xmin FROM Distributor;
UPDATE Distributor SET TotalSales = TotalSales + 7000;
SELECT *, Distributor.xmin FROM Distributor;


Here's the result:

memberid | totalsales | xmin
----------+------------+------
Michael | 1000.00 | 3624

memberid | totalsales | xmin
----------+------------+------
Michael | 8000.00 | 3639



to convert xmin to integer(thanks to AndrewSN, freenode #postgresql):
select int8in(xidout(xmin)) from company

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home