Thread: How do handle concurrent DML operations
Hi all,
I am building web application by using Oracle data base.
Because different uders will manipulate the same data record at the
same time.
Could someone please help me for the following questions:
1. How can I handel the concurrently DML (insert, update, delete)
opertions on same data record by different users? Can I use
synchronied method in update, insert, and delete opertionns? Or other
better method ??
2. When user 1 select data from table , user 2 update or delete the
date in the same table concurrently.
Do I need to set transaction isolation level to
TRANSACTION_READ_COMMITED or refreshRow() to get the updated data?
thanks all.
the driver isn't automagically going to update your records if someone changes them in the db while you have the result set loaded in your program. You need to do one of two things, either use pessimistic locking, ie select for update, and lock the row, or use optimistic locking and timestamp the updates so that when you commit your changes then you will see that the timestamp in your local record is different than the timestamp in the database and realize your data is stale. There is another way, but it is not ready yet, we are working on pl/j which is a java procedural language that would allow you to do the magic above. ie create a java procedure that you called on a trigger which would update your local copy of the data if someone else were to change it If you are interested and brave, have a look at plj.codehaus.org for the pl/j code, but beware this is alpha quality code. Dave On Thu, 2004-04-15 at 15:23, kenchen077 wrote: > Hi all, > > I am building web application by using Oracle data base. > Because different uders will manipulate the same data record at the > same time. > Could someone please help me for the following questions: > > 1. How can I handel the concurrently DML (insert, update, delete) > opertions on same data record by different users? Can I use > synchronied method in update, insert, and delete opertionns? Or other > better method ?? > > 2. When user 1 select data from table , user 2 update or delete the > date in the same table concurrently. > Do I need to set transaction isolation level to > TRANSACTION_READ_COMMITED or refreshRow() to get the updated data? > > thanks all. > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > > > > !DSPAM:4085512d14457238818707! > > -- Dave Cramer 519 939 0336 ICQ # 14675561
if user1 set Resultset rs to ResultSet.TYPE_SCROLL_SENSITIVE and using refreshRow() in rs. The rs might show the updated record within this rs updated by user2. The pessimistic locking seems a good method. How can I do it by programming or set up something in DBMS ? Could you please advise me? I need to set up row lock to prevent 2 users update same record concurrently. If I use synchronized method it may cause deadlock. Thanks pg@fastcrypt.com (Dave Cramer) wrote in message news:<1082479857.3069.178.camel@localhost.localdomain>... > the driver isn't automagically going to update your records if someone > changes them in the db while you have the result set loaded in your > program. > > You need to do one of two things, either use pessimistic locking, ie > select for update, and lock the row, or use optimistic locking and > timestamp the updates so that when you commit your changes then you will > see that the timestamp in your local record is different than the > timestamp in the database and realize your data is stale. > > There is another way, but it is not ready yet, we are working on pl/j > which is a java procedural language that would allow you to do the magic > above. ie create a java procedure that you called on a trigger which > would update your local copy of the data if someone else were to change > it > > If you are interested and brave, have a look at plj.codehaus.org for the > pl/j code, but beware this is alpha quality code. > > Dave > On Thu, 2004-04-15 at 15:23, kenchen077 wrote: > > Hi all, > > > > I am building web application by using Oracle data base. > > Because different uders will manipulate the same data record at the > > same time. > > Could someone please help me for the following questions: > > > > 1. How can I handel the concurrently DML (insert, update, delete) > > opertions on same data record by different users? Can I use > > synchronied method in update, insert, and delete opertionns? Or other > > better method ?? > > > > 2. When user 1 select data from table , user 2 update or delete the > > date in the same table concurrently. > > Do I need to set transaction isolation level to > > TRANSACTION_READ_COMMITED or refreshRow() to get the updated data? > > > > thanks all. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > > > > > > !DSPAM:4085512d14457238818707! > > > > > -- > Dave Cramer > 519 939 0336 > ICQ # 14675561 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
You should very seriously read and understand the following http://www.postgresql.org/docs/7.4/interactive/mvcc.html On Wed, 2004-04-21 at 12:35, kenchen077 wrote: > if user1 set Resultset rs to ResultSet.TYPE_SCROLL_SENSITIVE and using > refreshRow() in rs. The rs might show the updated record within this > rs updated by user2. If this is inside a transaction and you will not see the other transactions commits, even if you do a refresh row, see above documentation. Dave > > The pessimistic locking seems a good method. How can I do it by > programming or set up something in DBMS ? Could you please advise me? > I need to set up row lock to prevent 2 users update same record > concurrently. If I use synchronized method it may cause deadlock. > > Thanks > > > > pg@fastcrypt.com (Dave Cramer) wrote in message news:<1082479857.3069.178.camel@localhost.localdomain>... > > the driver isn't automagically going to update your records if someone > > changes them in the db while you have the result set loaded in your > > program. > > > > You need to do one of two things, either use pessimistic locking, ie > > select for update, and lock the row, or use optimistic locking and > > timestamp the updates so that when you commit your changes then you will > > see that the timestamp in your local record is different than the > > timestamp in the database and realize your data is stale. > > > > There is another way, but it is not ready yet, we are working on pl/j > > which is a java procedural language that would allow you to do the magic > > above. ie create a java procedure that you called on a trigger which > > would update your local copy of the data if someone else were to change > > it > > > > If you are interested and brave, have a look at plj.codehaus.org for the > > pl/j code, but beware this is alpha quality code. > > > > Dave > > On Thu, 2004-04-15 at 15:23, kenchen077 wrote: > > > Hi all, > > > > > > I am building web application by using Oracle data base. > > > Because different uders will manipulate the same data record at the > > > same time. > > > Could someone please help me for the following questions: > > > > > > 1. How can I handel the concurrently DML (insert, update, delete) > > > opertions on same data record by different users? Can I use > > > synchronied method in update, insert, and delete opertionns? Or other > > > better method ?? > > > > > > 2. When user 1 select data from table , user 2 update or delete the > > > date in the same table concurrently. > > > Do I need to set transaction isolation level to > > > TRANSACTION_READ_COMMITED or refreshRow() to get the updated data? > > > > > > thanks all. > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > > > > > > > > > > > > > > > > > > -- > > Dave Cramer > > 519 939 0336 > > ICQ # 14675561 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > > > > !DSPAM:408d34f8160166163863926! > > -- Dave Cramer 519 939 0336 ICQ # 14675561