Alexander Tsirel wrote: > > Hi all, > > I've got a strange lock when trying to modify data in a table from two > simultaneous sessions. > I did following: > > 1) Connect to database from sqlcli (let it be first session) > [EMAIL PROTECTED]:~$ sqlcli-wrapper -d testdb -u dba,dba > Welcome to the SAP DB interactive terminal. > > Type: \h for help with commands > \q to quit > > 2)Create test table > create table testlock (id integer) > 0 rows affected (24 msec) > > 3)Set autocommit off > sqlcli testdb=> \a > Autocommit mode switched OFF > > 4)Then started another session (let it be second) and set autocommit false > > 5)Inserted into testlock table a row from first session > sqlcli testdb=> insert into testlock values(1) > 1 row affected (1 msec) > > 6)Inserted int testlock table a row from second session > sqlcli testdb=> insert into testlock values(2) > 1 row affected (1 msec) > > 7) Selecting all from testlock table from first session > sqlcli testdb=> select * from testlock > > And I got nothing.... It seems that table is locked and we can't do > anything. > If in this moment we commit second session we get everything: > sqlcli testdb=> select * from testlock > | ID | > | -------------- | > | 1 | > | 2 | > 2 rows selected (2 msec) > > > Transaction isolation level is set by default to read_commited. > Information about kernel and db_instance: > dbmcli on testdb>version > OK > version,os,dbroot,logon,code,swap > "7.5.0","UNIX","/usr/lib/maxdb/7.5.00",True,ASCII,2 > > Could you explain this situation?
Read committed does not mean, that you are allowed to ignore rows which haven't been commited/rollbacked. Therefore your first session goes through the whole table (you do not have any qualification/index in which would allow any other strategy). It sees row 1, its own, puts the result into the corresponding structure, comes to the next row, which is EXCLUSIVE locked by another session. Now the first session has to wait until second session has decided what to do with this row: commit or rollback. Then the first session will return the second row (or not find it any more if second session rollbacked) and go on further. >From our point of view and with this isolation level the behavior is correct. Elke SAP Labs Berlin > > To my best knoledge we should recieve data from first session i.e. value > 1. Am I wrong? > > Alexander Tsirel > > > > > > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
