November 21, 2005 at 5:50 am
I am connecting to mssql using jdbc. I try to make a transaction
.......
Connection con = dataSource.getConnection();
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement(someInsertSqlCode);
setInt(1, 123446657); //some field
ps.executeUpdate();
.......
Then applcation waits for user to choose "accept" and call commit:
con.commit();
Trouble : Before commit no one else can read something from this table.It's blocked. i can get acces to it from another aplcation only after commit . I tried to set con.setTransactionIsolation() - this didn't work
November 21, 2005 at 6:44 am
One of the first rules you should observe with database transactions... They should be
AS SHORT AS POSSIBLE...
This means that you should never have any user interactive process that keeps your transaction open as having the transaction open will keep locks on the rows/pages/tables to ensure the data remains consistent...
If your goal is to lock a certain row so no other user can modify it, you'll need to add an extra column called "lock" (for example) to your table and set it true before displaying the dialog, commit that change, display the dialog, and then start another transaction to continue your work.
If you give us some more information about what reasons you have for keeping the transaction perhaps we can help come up with an appropriate method to work around it for you. For example, one part of my app needs to lock a whole "sheet" of information - I store a lockID flag (each time you lock it, the lockID is randomly generated GUID) and the time the lock was acquired. Every X minutes, the lock must be reacquired by the client app, otherwise the lock "times out" - essentially, if another user tries to acquire the lock and the lock update time is more than X minutes old, it assumes the original locking client app must have crashed and doesn't actually need the lock.
Cheers
November 21, 2005 at 8:27 am
I am creating a web application(tomcat). User uploads csv files on server. They stored in databease(or in directory). Then user check some files in uploded list and press "import". csv files have about 50 columns and rather big number of rows. There are a lot diffrent check for imported data(for every field, some of them are checked in bd). At this time user see status of the process and can cancel it. It's a whole big transaction with several tables. After finsing user can can get reports about errors in each line and rollback transtaction if he wants(customer wants so). It's a web interface - user can never close transaction.. Does this problem has any decision?
November 21, 2005 at 9:09 pm
To overcome this, you could always import the data into some sort of staging area, let the user manipulate it (all with tiny transactions - it's just their staged data - no one else would be mucking with it anyway). Then, once the user is happy, you can import the staged data into your permanent database...
Also, it's been a while since I've done things in Java/Tomcat/Servlets, etc but I don't see how you are keeping the connection object (and hence the transaction) around between page calls?
In any case, if there is a lot of data and parsing which will take a while, import it into a temporary table (either a proper SQL temp table, or a table you create and drop just for this particular import) and then, within SQL, move the data from the temp table to the permanent table - it will be a lot faster and the locking duration on the shared data will be less.
November 22, 2005 at 2:06 am
I am keeping connection object in thread for user(authorization through Active Directory) .. All treads are keeped in pool - static object
I have overcomed this trouble by another way, set transaction isolation level :
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED)
for all (reading) operations...
Now it's working. May be it's a wrong way - I'l see this in future
Except import operations there will be also editing through web interface
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply