Database Transaction

[Former Member]

Hi,

In our project we have EJB's(CAS) consumed in CAS. The EJB's communicate with SQL/Oracle DB's through standard queries(insert, update etc). Now we want to implement "Database Transaction" i.e, if the any one user from front-end is accessing the particular table of the DB, no other user should be able to modify the same table simultaneously. How is thisfeasible?

Regards,

Jagannathan R

[markus_doehr2]

> In our project we have EJB's(CAS) consumed in CAS. The EJB's communicate with SQL/Oracle DB's through standard queries(insert, update etc). Now we want to implement "Database Transaction" i.e, if the any one user from front-end is accessing the particular table of the DB, no other user should be able to modify the same table simultaneously. How is thisfeasible?

So are you talking about Oracle or Microsoft SQL Server? The forum you opened that message is for SQL Server.

Can you please tell what you mean with "if one user is accessing a table no other user should be able to modifiy the same table simultaneously"? If you'd do that you'd heavily decrease performance.

Markus

[Former Member]

Hi Markus,

We are using Microsoft SQL Database.

Also, what i mean is when a user clicks on any button which say writes into "table 1". At the same time a second user should not be allowed to write/ access the same table.

Regards

Jagannathan R

[markus_doehr2]

> Also, what i mean is when a user clicks on any button which say writes into "table 1". At the same time a second user should not be allowed to write/ access the same table.

The same table or the same row?

What sense does it make to lock a full table if someone inserts an entry? I'm just trying to understand.

Markus

[Former Member]

Marcus,

Exactly. Even the second user(or n-users) should not be allowed to insert any values. So, its not pertaining to a row but the table as a whole.

Jagan

[markus_doehr2]

> Exactly. Even the second user(or n-users) should not be allowed to insert any values. So, its not pertaining to a row but the table as a whole.

I'm not sure if this locking mechanism is supported since it would create a HUGE performance penalty because all modifications to the table would be serialized.

Can you tell us why you need that?

Markus

[Former Member]

It is the businness/functionality requirement which is prompting us to think that way. Can you please elaborate the method that you are talking about even though it might take a huge toll on the performance?

[markus_doehr2]

> It is the businness/functionality requirement which is prompting us to think that way.

I'm sure there are technical other ways of accomplishing what's needed, such as 'dirty reads'.

> Can you please elaborate the method that you are talking about even though it might take a huge toll on the performance?

The "method" is the configuration of the database engines isolation level:

http://msdn.microsoft.com/en-en/library/ms173763.aspx

Markus