JDBC Sender Adapter

[Former Member]

Hi,

I have a Query SQL Statement like this: SELECT ...; UPDATE ...;

Above is all listed in the Query SQL Statement and not seperated into Query and UPDATE.

Need verification on the following:

1) The SQL listed in Query SQL Statement is executed in 1 and only 1 database transaction.

2) Setting the transaction isolation level to either repeatable_read or serializable prevent others from modifying the database.

What I need is to be able to select data from various tables and prevent others from adding new records to these tables while my transaction is executing (this is why point 1 relevant).

Best Regards,

Daniel

[VijayKonam]

Daniel,

The best solution for this would be to call SPs rather than the SQL statements directly. That way you would have complete control over the transaction isolation at database level.

One more thing, I am not sure if 2 queries are mentioned in the adapter then how would they get executed at database level..

VJ

[Former Member]

Hi,

Thanks for your reply.

Using stored procedures is not an option I'm afraid.

To ensure 2 or more queries being executed in the same transaction can always be achieved by using BEGIN TRANSACTION...COMMIT TRANSACTION in the SQL.

Also I can ensure locking of tables from the SQL by using ...WITH (TABLOCK, HOLDLOCK, UPDLOCK). The later however is not pretty and not optimal.

As far as I understand the SERIALIZED isolation level it will disallow new records to be inserted into tables if and only if the new records fall into the dataset of an executing transaction.

Does this mean that if new records match the WHERE clause of the executing transaction they will but in queue until the current transaction finishes?

Best Regards,

Daniel