Table locked after insert before commit

Discussion forum about all things SqlBase.
Marcel

Table locked after insert before commit

Post by Marcel » 23 Nov 2015, 00:46

I use C# .NET to connect to SQLBase. Connection string: @"host=LOCALHOST;ServerName=Server1;Initial Catalog=ahne;isolation=RL;autocommit=false; ReadCommitted=RL; transport=TCP;Port=2155"

After inserting in the database, I immediately want to read the rowid of the inserted row, before committing. (committing is much later) But the table is locked until I execute the commit. I attached a zip file showing screen prints of SQL command center:

Connections to the database having isolation level "RL".
Locks for the table that I inserted the row into the value "UXI". (I tried to find out what this screen means but could not find anything in the
documenation) But I gues this represents there is some kind of lock.

My question is: what settings in the connection string or elsewhere shoud I apply so that I can read data from the table immediately after insert, before commit.

Many thanks, Marcel
You do not have the required permissions to view the files attached to this post.

Mike Vandine

Re: Table locked after insert before commit

Post by Mike Vandine » 23 Nov 2015, 08:17

Hi Marcel,

You should be able to read something that you've inserted if the transaction is the same. I suspect it's not...

You've got:

isolation=RL;ReadCommitted=RL

I think the ReadCommitted=RL is redundant. I think this is specifically for RC isolation level and you're already saying you want to connect using RL.

I would modify the sql.ini file to add two lines:

autorc=1
defaultrc=1

This will convert *every* connection that comes in as an RL isolation level to RC1. Try to see if that setting works for you. If it doesn't, put in a defaultrc=2 and try that. If that doesn't work, try defaultrc=3.

If none of these settings works, then I think the transaction is not the same and you might need to investigate that.

BTW, I think the lock display means that there is an Exclusive Insert lock being held.

Best regards,

Marcel

Re: Table locked after insert before commit

Post by Marcel » 23 Nov 2015, 12:36

Hi Mike,

Thanks for your swift answer. The databaselock is now gone. But retrieving the rowid of the newly inserted / updated row (before commit) is not giving me what I want. If I insert / update a row and then want to retrieve it's new rowid (before commit) it does not find it (insert) or it returns the old one (update). In SQLTalk there is no problem. I can retrieve the new rowid before commit.

Retrieving rowid before the commit makes things much easer because . If I wait to retrieve rowid after commit, I have to start all over again and iterate over all updates that were part of the committed transaction.

in sql.ini, i tried defaultrc=1, defaultrc=2 and defaultrc=3.


Greets, Marcel

Mike Vandine

Re: Table locked after insert before commit

Post by Mike Vandine » 24 Nov 2015, 07:54

Hi Marcel,

I'll need a small testcase, preferable against the ISLAND database.

Best regards,

Marcel

Re: Table locked after insert before commit

Post by Marcel » 25 Nov 2015, 13:40

Ok thanks. I'll need some time to make the sample.

Greets

Marcel

Re: Table locked after insert before commit

Post by Marcel » 29 Nov 2015, 13:16

Hi Mike,

The sample is a project in visual studio express 2015 for windows desktop. I removed newtonsoft package to reduce filesize. Please install newtonsoft again via nuget package manager with command
' Install-Package Newtonsoft.Json' .

So after insert / update, I want to immediately retrieve the new rowid, before even a commit has been executed. Before commit, because further on in the transaction an error might occur that requires a rollback.

If I retrieve the rowid after update / insert, the old rowid (in case of update) or no rowid (in case of insert) is found. If I execute a commit before retrieving the rowid, the new rowid is returned, proving there is nothing wrong with the code.

In SQL talk, SQLBase acts the way I want: the new rowid is returned after insert / update. No commit is required.

Tx, Marcel
You do not have the required permissions to view the files attached to this post.

Marcel

Re: Table locked after insert before commit

Post by Marcel » 29 Nov 2015, 13:25

O, forgot to say in my previous message: the test transactions are against the 'contact' table of the 'islands' database. For simplicity of the example, I hardcoded an instance of a contact having rowid 'EHAAAAAAACAAAAAAGGAKAAAAAAAAAAAINGACAAAA' . This rowid might require a change in the 'program.cs' section.

greets, Marcel

Marcel

Re: Table locked after insert before commit

Post by Marcel » 08 Dec 2015, 18:39

well, I guess its just the way it works in .NET. I will retrieve the new rowid's after final commit. I already created the software, no need anymore to go into this issue.

tx, Marcel

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests