473,837 Members | 1,469 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Online & update races

Hello there,

I am thinking how to solve another typical problem of online systems
with combination of thin client... Imagine simple case, 2 users are going to
edit 'same' datas. Both see on the 'screen' the same, after they started
edit them. First one changes datas and submit changes (sucessfully).
Database (set tables - inserts/updates/deleting) was changed. At this point,
datas which second user is watching are not valid anymore. They are outdated
and he should refresh or 'merge' changes. If he will 'submit' his datas, he
can delete change of first user. These time races can occurs accross various
part of complex system, so, there is no way how to 'lock' function - e.g.
second user will see "wait, someone edits right now".

What is the best way to solve this general problem - on DB layer as
possible ? (using PlSql, triggers, ...) - anyone have experimence, or is
there any project, which allready did something like this ? Database server
is PgSql, as you probably expect :) I am not too much into PgSql internals
but I belive, there is way how to solve these problems better than
application layer...

Locking records/tables is not possible at all. It is yeasy to add some
counter and check version in system itselfs, but something more general
(e.g. UPDATE SQL statement will return error, if record
'timestamp/changeid/...' will be mismatched, whatever) is much nicer
solution. It makes 'system' itselfs simpler, which is good.

Another solution is more 'organisation work flow'. I belive, combination
of system (DB) and workflow is the best solution.

All these time-related changes are done within one transaction.

I will also appreciate any links to web resources, talking about this
problem. I didn't find anything usefull around.

Thank you again,

Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
4 1918
Is a dump and reload required when going from beta1 to beta3?

Chris

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #2

I used to do it this way :

Suppose you have a web form to edit data from a table... you add a field
in your table which contains a version identifier for that data, then you
UPDATE ... WHERE ... AND version_id = the old version id. The version_id
is passed around in a session variable or in hidden form fields. The
hidden form fields are better because they will prevent breakage if the
same user edits the same data in different windows, or refreshes his
browser window at the wrong time.
Then, if the UPDATE has a rowcount of 1, you know it's OK, but if it has
a rowcount of 0, you know something happens, and can check if the record
still exists and its version id was modified, or if the record was deleted.
A version id can be a counter, a sequence... it can also be a MD5 of the
row contents for instance, its sole purpose being to detect change. Using
a sequence might be the easiest.
This way works but still looks like band-aid ; moreover, if you do a
complex operation which modifies several tables, you have to take care of
modification order, and the problem becomes more complex.
It would be nice to have a framework for that kind of thing which is
common in web apps.
One of postgresql's good points is that it does not lock things, thanks
to MVCC, unlike MySQL which locks the table on every write. This model is
in the same spirit than MVCC, because it will not prevent reads to records
which are being updated.
However, a recurrent problem in web applications is that there is no
"logout", logout can only be implemented with certainty using timeouts, so
you can't use locking, because you really don't know when the locks will
be released. If you use locking, some information will get locked waiting
for a timeout if a user closes his browser without explicitely logging out
; besides you'd have to have a cron to log users out as a disconnected
user, by definition makes no action to signal the fact that h's gone away.
You could implement this by adding a version_id serial field to the
relevant tables, and then an ON UPDATE trigger which would check that the
version_id of the updater is the same than the version_id in the updated
row, or else raise an exception. You can also have a special value to
bypass checks, to be able to update in all cases, and not get stuck if you
have a problem. The trigger would then increment the version_id before
updating.

What do you think ?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
Lada 'Ray' Lostak wrote:
I will also appreciate any links to web resources, talking about this
problem. I didn't find anything usefull around.


I'm working with developing a fairly big warehouse management system, and
there we see this problem every day. We've settled (many years ago) for the
following:

loop
begin
select * from my_table where status = :status
(read into record structure)

update my_table set a=1, b=2 where and status = :status and
another_status = :status2 and lates_updater =:record.latest _updater and
latest_date = :record.latest_ date and latest_time = :record.latest_ time

commit
exit loop
exception
when transaction_con flict | no_such_row =>
rollback
delay small time
end loop;

transaction_con flict | no_such_row should be interpreted as no row matched.
The flaw this design has, is that you can't separate 'real transaction
conflicts' with situation where other conditions failed, as no row had
another_status = status2

However, we find the design good enough to keep using it. It has the
advantage that no table is ever looked, we don't use select for update at
all.

--
/Björn
-------------------------------------------------------------------
http://lundin.homelinux.net
Registered Linux User No. 267342 <http://counter.li.org>
Nov 23 '05 #4

Hi,
Suppose you have a web form to edit data from a table... you add a field
in your table which contains a version identifier for that data, then you
UPDATE ... WHERE ... AND version_id = the old version id. The version_id
is passed around in a session variable or in hidden form fields. The
hidden form fields are better because they will prevent breakage if the
same user edits the same data in different windows, or refreshes his
browser window at the wrong time.
Then, if the UPDATE has a rowcount of 1, you know it's OK, but if it has
a rowcount of 0, you know something happens, and can check if the record
still exists and its version id was modified, or if the record was deleted. A version id can be a counter, a sequence... it can also be a MD5 of the
row contents for instance, its sole purpose being to detect change. Using
a sequence might be the easiest.
This way works but still looks like band-aid ; moreover, if you do a
complex operation which modifies several tables, you have to take care of
modification order, and the problem becomes more complex.
It would be nice to have a framework for that kind of thing which is
common in web apps.
One of postgresql's good points is that it does not lock things, thanks
to MVCC, unlike MySQL which locks the table on every write. This model is
in the same spirit than MVCC, because it will not prevent reads to records
which are being updated.
However, a recurrent problem in web applications is that there is no
"logout", logout can only be implemented with certainty using timeouts, so
you can't use locking, because you really don't know when the locks will
be released. If you use locking, some information will get locked waiting
for a timeout if a user closes his browser without explicitely logging out
; besides you'd have to have a cron to log users out as a disconnected
user, by definition makes no action to signal the fact that h's gone away.
You could implement this by adding a version_id serial field to the
relevant tables, and then an ON UPDATE trigger which would check that the
version_id of the updater is the same than the version_id in the updated
row, or else raise an exception. You can also have a special value to
bypass checks, to be able to update in all cases, and not get stuck if you
have a problem. The trigger would then increment the version_id before
updating.


Thank you for reply. I think this is basically the 'only' way how to solve
the problem. Have some 'row changes count'. This scheme is easy
implementable for smaller systems. But as you wrote above, if you prepare
'user datas' for various tables, joins, whatever, it is very hard to take
care of this 'modified serial'. I would like to move this way to 'database'
itselfs somehow.

I was thinking abotu something like this... But I don't know PgSql
internals, so, I don't know if it is possible...

I suppose DB engine have to 'hold' some kind of row version' (chnages count,
timestamp, combination, whatever) - because it should be needed while
transactions. If DB engine can 'collect' within one transaction these ID's
which were used while selecting datas, I can keep then in 'POST' data and
verify (while transaction) if they are still valid. I have no clue if this
is possible with PlSQL or another server-side scripting language or some
improvement of PqSql is needed. And if improvement is needed, if it is
possible. And if it is possible, if some PG developer will want to do that -
and if they will like this kind of improvement. I am open to pay for solving
this problem, because the system we are doing will be used commerically. And
I think, this is general problem, which every bigger system have to solve.
Sooner or later.

I fully agree with you, that locking is not a way. But is there any other
'more automatized' way than take care of row versions by ourself ?

But before contacting developers, I would like to ask other about
opinions...

Have a nice day,
Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2089
by: David | last post by:
I am setting up a web community with a feature that will display whether or not a member is currently online. I intend on implementing this by holding a column in the dbase simply called online. When a user logs on, the status of this will be updated to yes. When the user clicks to log out, we simply change the state of this field to no. The problem comes when a user does not execute the log out script and simply closes their browser,...
2
2856
by: Atz | last post by:
Hi to all ! Im using MySql front and PHP 5 for some web shop. I didn't try it so far but i guess that the online data insertation ( accept costs and time ) should be the same process like for local connection. Problem: I have to create online ( on remote server ) some 6 tables and some 20 fields with MySql front. Ok. This shouldn't be great problem ( i hope :-)).
20
3313
by: Pierre Fortin | last post by:
Hi! "Python Essential Reference" - 2nd Ed, on P. 47 states that a string format can include "*" for a field width (no restrictions noted); yet... >>> "%*d" % (6,2) # works as expected ' 2' Now, with a mapping....
0
1419
by: chan | last post by:
how to apply online update function into program (the effect just like Norton system work live update) The situation is below: I want to develop a program that contains some product information (in the database) for customer choosing.
1
3798
by: Zaidan | last post by:
I am running Excel2000 under WIN98 2nd edition, and I am writing a VBA code (I will consider using javascript if I have to) that does the following, at the user command: 1- Start MS Explorer and go to my website. Login (enter ID and Password) 2- It will go and update some prices of some products that I sell 3- It will add new products or cancel some product I already have someone who wrote me in JavaScript a web form (no documentation...
0
1232
by: sitpost | last post by:
Here is a list of states with close Senate Races. The tie-breakers are Libertarian Candidates (a party that actually represents the widest majority of conservatives) in the U.S. Texas has become "quite interesting" with all of that state's redistricting! If enough people voted for the Libertarian candidates; it would promptly come to the attention of both DEM & GOP parties! ALASKA: Scott Kohlhaas
1
1455
by: dirk van waes | last post by:
Hello everyone, Being complete newbie in asp.net I am trying to make an example which works with a very simple database. First I made my project in VS- vb.net, draging an oledbconnection and an oledbdataadapter from the toolbox into my form. Everything worked fine on my local computer. I was able to search, update, delete and insert into my klanten.mdb database.
10
2310
by: Breana | last post by:
Ok i found this a while back and i am trying to mod it so it works but it keeps not updating the users online and kills my other code? Well i got it working but it dont update when i login? It says 14 guest now, and i am logged in so it sould ream me as member..... And how do i make a cron job, the tutorial says it lowrs site badwith and it runs better updating records?
6
1605
by: cargo | last post by:
Hello I have a SQL problem that I not sure of how to categorise. I have a table like this - 3 columns (Col_1,Col_2,Col_3) and want to create a 4th column =New_Column Col_1 Col_2 Col_3 New_Column 1 3 15.3 0.3 = 15.3-15.0 1 2 15.8 0.8 15.8-15.0
0
9839
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10564
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9396
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7806
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6998
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5668
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4474
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4039
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3123
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.