same session can't see uncommit data

same session can't see uncommit data

Postby wjchen119 » Fri 04 Nov 2011 05:24

Hello, I use Delphi XE2, happened some questions:
Beginning "begin transaction", and then to insert update some data, not yet commit before same session can't see changed data, after commit the data correct。
wjchen119
 
Posts: 4
Joined: Fri 04 Nov 2011 05:07

Postby AlexP » Mon 07 Nov 2011 08:17

Hello,

Please give a more detailed description of the problem, how you insert data in the table, where you are trying to view inserted data, etc. Or send a small sample to alexp*devart.com.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Postby folhamatic » Tue 08 Nov 2011 02:26

Hi!

i have the same problem:

Test Code (Doesn´t Read Uncommited Data):

Code: Select all
try
           {

               using (FolhamaticContext ctx = new FolhamaticContext())
               {

                   TransactionOptions options = new TransactionOptions();
                   options.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
                   using (System.Transactions.TransactionScope lTranscacao = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, options))
                   {


                     var before = ctx.UmEntity.ToList().AsEnumerable();
                     
                       UmEntity um = new UmEntity();
                       um.idempresa = 1;
                       um.idstatus = 1;
                       um.idultusuario = 1;
                       um.dtalteracao = DateTime.Now;
                       um.idum = 123;
                       um.sigla = "TG";
                       um.descricao = "testegustavo";

                       ((IObjectContextAdapter)ctx).ObjectContext.AttachTo("UmEntity", um);
                       ctx.Entry(um).State = EntityState.Added;

                       ctx.SaveChanges();

                       [color=red] //I should be able to read the uncommited data here[/color]
                       var after= ctx.UmEntity.ToList().AsEnumerable();
                       

                       lTranscacao.Complete();
                   }
               }
           }
           catch (Exception ex)
           {

               MessageBox.Show(ex.ToString());
           }


PostGreSQL profile:


Code: Select all
2011-11-07 23:06:28 BRT LOG:  comando: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2011-11-07 23:06:28 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:28 BRT LOG:  comando: BEGIN
2011-11-07 23:06:28 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:33 BRT LOG:  duração: 0.000 ms  análise de PRSTMT214497261456061392: SELECT

   "Extent1".idum AS idum,

   "Extent1".idstatus AS idstatus,

   "Extent1".idultusuario AS idultusuario,

   "Extent1".dtalteracao AS dtalteracao,

   "Extent1".idempresa AS idempresa,

   "Extent1".sigla AS sigla,

   "Extent1".descricao AS descricao

   FROM dbo.tblum AS "Extent1"
2011-11-07 23:06:33 BRT LOG:  duração: 0.000 ms  ligação PRSTMT214497261456061392/PORTAL214497261456061392: SELECT

   "Extent1".idum AS idum,

   "Extent1".idstatus AS idstatus,

   "Extent1".idultusuario AS idultusuario,

   "Extent1".dtalteracao AS dtalteracao,

   "Extent1".idempresa AS idempresa,

   "Extent1".sigla AS sigla,

   "Extent1".descricao AS descricao

   FROM dbo.tblum AS "Extent1"
2011-11-07 23:06:33 BRT LOG:  executar PRSTMT214497261456061392/PORTAL214497261456061392: SELECT

   "Extent1".idum AS idum,

   "Extent1".idstatus AS idstatus,

   "Extent1".idultusuario AS idultusuario,

   "Extent1".dtalteracao AS dtalteracao,

   "Extent1".idempresa AS idempresa,

   "Extent1".sigla AS sigla,

   "Extent1".descricao AS descricao

   FROM dbo.tblum AS "Extent1"
2011-11-07 23:06:33 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:39 BRT LOG:  comando: SELECT version()
2011-11-07 23:06:39 BRT LOG:  duração: 10.000 ms
2011-11-07 23:06:39 BRT LOG:  comando: show integer_datetimes
2011-11-07 23:06:39 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:39 BRT LOG:  comando: SET autocommit=true
2011-11-07 23:06:39 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:39 BRT LOG:  comando: SET datestyle=ISO ; SET client_encoding='UTF8'
2011-11-07 23:06:39 BRT LOG:  duração: 10.000 ms
2011-11-07 23:06:39 BRT LOG:  comando: SELECT oid FROM pg_type WHERE typname like 'geometry'
2011-11-07 23:06:39 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:39 BRT LOG:  comando: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2011-11-07 23:06:39 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:39 BRT LOG:  comando: BEGIN
2011-11-07 23:06:39 BRT LOG:  duração: 10.000 ms
2011-11-07 23:06:44 BRT LOG:  duração: 0.000 ms  análise de PRSTMT36397649456072172: INSERT INTO dbo.tblum(idstatus, idultusuario, dtalteracao, idempresa, sigla, descricao)

   VALUES ($1, $2, $3, $4, $5, $6)

    RETURNING idum
2011-11-07 23:06:44 BRT LOG:  duração: 0.000 ms  ligação PRSTMT36397649456072172/PORTAL36397649456072172: INSERT INTO dbo.tblum(idstatus, idultusuario, dtalteracao, idempresa, sigla, descricao)

   VALUES ($1, $2, $3, $4, $5, $6)

    RETURNING idum
2011-11-07 23:06:44 BRT DETALHE:  parâmetros: $1 = '1', $2 = '1', $3 = '2011-11-08 00:06:34.296099', $4 = '1', $5 = 'TG', $6 = 'testegustavo'
2011-11-07 23:06:44 BRT LOG:  executar PRSTMT36397649456072172/PORTAL36397649456072172: INSERT INTO dbo.tblum(idstatus, idultusuario, dtalteracao, idempresa, sigla, descricao)

   VALUES ($1, $2, $3, $4, $5, $6)

    RETURNING idum
2011-11-07 23:06:44 BRT DETALHE:  parâmetros: $1 = '1', $2 = '1', $3 = '2011-11-08 00:06:34.296099', $4 = '1', $5 = 'TG', $6 = 'testegustavo'
2011-11-07 23:06:44 BRT LOG:  duração: 20.000 ms
2011-11-07 23:06:53 BRT LOG:  comando: SELECT version();
2011-11-07 23:06:53 BRT LOG:  duração: 10.000 ms
2011-11-07 23:06:53 BRT LOG:  comando: SET DateStyle=ISO;
   SET bytea_output=escape;
   SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid
     FROM pg_database WHERE oid = 64105
2011-11-07 23:06:53 BRT LOG:  duração: 10.000 ms
2011-11-07 23:06:53 BRT LOG:  comando: set client_encoding to 'UNICODE'
2011-11-07 23:06:53 BRT LOG:  duração: 0.000 ms
2011-11-07 23:06:59 BRT LOG:  comando: select * from dbo.tbum
2011-11-07 23:06:59 BRT ERRO:  relação "dbo.tbum" não existe no caracter 15
2011-11-07 23:06:59 BRT COMANDO:  select * from dbo.tbum
2011-11-07 23:07:06 BRT LOG:  comando: select * from dbo.tblum
2011-11-07 23:07:06 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:06 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 1114
2011-11-07 23:07:06 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=1114
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT format_type(oid,10) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=1043
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT format_type(oid,54) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:07:06 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:06 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=1043
2011-11-07 23:07:06 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:16 BRT LOG:  comando: SELECT version()
2011-11-07 23:07:16 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:16 BRT LOG:  comando: show integer_datetimes
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:16 BRT LOG:  comando: SET autocommit=true
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:16 BRT LOG:  comando: SET datestyle=ISO ; SET client_encoding='UTF8'
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:16 BRT LOG:  comando: SELECT oid FROM pg_type WHERE typname like 'geometry'
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:16 BRT LOG:  comando: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:16 BRT LOG:  comando: BEGIN
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms  análise de PRSTMT212327929856104168: SELECT

   "Extent1".idum AS idum,

   "Extent1".idstatus AS idstatus,

   "Extent1".idultusuario AS idultusuario,

   "Extent1".dtalteracao AS dtalteracao,

   "Extent1".idempresa AS idempresa,

   "Extent1".sigla AS sigla,

   "Extent1".descricao AS descricao

   FROM dbo.tblum AS "Extent1"
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms  ligação PRSTMT212327929856104168/PORTAL212327929856104168: SELECT

   "Extent1".idum AS idum,

   "Extent1".idstatus AS idstatus,

   "Extent1".idultusuario AS idultusuario,

   "Extent1".dtalteracao AS dtalteracao,

   "Extent1".idempresa AS idempresa,

   "Extent1".sigla AS sigla,

   "Extent1".descricao AS descricao

   FROM dbo.tblum AS "Extent1"
2011-11-07 23:07:16 BRT LOG:  executar PRSTMT212327929856104168/PORTAL212327929856104168: SELECT

   "Extent1".idum AS idum,

   "Extent1".idstatus AS idstatus,

   "Extent1".idultusuario AS idultusuario,

   "Extent1".dtalteracao AS dtalteracao,

   "Extent1".idempresa AS idempresa,

   "Extent1".sigla AS sigla,

   "Extent1".descricao AS descricao

   FROM dbo.tblum AS "Extent1"
2011-11-07 23:07:16 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: PREPARE TRANSACTION 'fe27e6dd-d86b-406d-92be-74c1bed374ff'
2011-11-07 23:07:30 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: PREPARE TRANSACTION 'e0a700d3-9fdc-4b45-8540-a38dc189b379'
2011-11-07 23:07:30 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: PREPARE TRANSACTION 'ab37f3cb-58c5-48b9-b2fc-f5b82957e708'
2011-11-07 23:07:30 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: COMMIT PREPARED 'fe27e6dd-d86b-406d-92be-74c1bed374ff'
2011-11-07 23:07:30 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: COMMIT
2011-11-07 23:07:30 BRT AVISO:  não há uma transação em execução
2011-11-07 23:07:30 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: COMMIT PREPARED 'e0a700d3-9fdc-4b45-8540-a38dc189b379'
2011-11-07 23:07:30 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: COMMIT
2011-11-07 23:07:30 BRT AVISO:  não há uma transação em execução
2011-11-07 23:07:30 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: COMMIT PREPARED 'ab37f3cb-58c5-48b9-b2fc-f5b82957e708'
2011-11-07 23:07:30 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:30 BRT LOG:  comando: COMMIT
2011-11-07 23:07:30 BRT AVISO:  não há uma transação em execução
2011-11-07 23:07:30 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: select * from dbo.tblum
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:36 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 1114
2011-11-07 23:07:36 BRT LOG:  duração: 10.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=1114
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=23
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT format_type(oid,10) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=1043
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT format_type(oid,54) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:07:36 BRT LOG:  duração: 0.000 ms
2011-11-07 23:07:36 BRT LOG:  comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
     FROM pg_type WHERE oid=1043
2011-11-07 23:07:36 BRT LOG:  duração: 10.000 ms


Test Code "WORKAROUND" (Read Uncommited Data):
*** Don´t think this is the best solution(TransactionScope should work)


Code: Select all
DbTransaction transaction=null;

           try
           {

               using (FolhamaticContext ctx = new FolhamaticContext())
               {

                   if (((IObjectContextAdapter)ctx).ObjectContext.Connection.State != ConnectionState.Open)
                   {

                       ((IObjectContextAdapter)ctx).ObjectContext.Connection.Open();
                   }
                 
                    transaction = ((IObjectContextAdapter)ctx).ObjectContext.Connection.BeginTransaction();

                       var before = ctx.UmEntity.ToList().AsEnumerable();

                       UmEntity um = new UmEntity();
                       um.idempresa = 1;
                       um.idstatus = 1;
                       um.idultusuario = 1;
                       um.dtalteracao = DateTime.Now;
                       um.idum = 123;
                       um.sigla = "TG";
                       um.descricao = "testegustavo";

                       ((IObjectContextAdapter)ctx).ObjectContext.AttachTo("UmEntity", um);
                       ctx.Entry(um).State = EntityState.Added;

                       ctx.SaveChanges();

  [color=red] //I am able to read the uncommited data now[/color]
                       var after = ctx.UmEntity.ToList().AsEnumerable();

                       transaction.Commit();
               }
           }
           catch (Exception ex)
           {

               MessageBox.Show(ex.ToString());

               transaction.Rollback();
           }


PostGreSQL profile:

2011-11-07 23:14:43 BRT LOG: duração: 0.000 ms análise de PRSTMT119576063956551142: BEGIN
2011-11-07 23:14:43 BRT LOG: duração: 0.000 ms ligação PRSTMT119576063956551142/PORTAL119576063956551142: BEGIN
2011-11-07 23:14:43 BRT LOG: executar PRSTMT119576063956551142/PORTAL119576063956551142: BEGIN
2011-11-07 23:14:43 BRT LOG: duração: 0.000 ms
2011-11-07 23:14:54 BRT LOG: duração: 10.000 ms análise de PRSTMT213055026756562312: SELECT

"Extent1".idum AS idum,

"Extent1".idstatus AS idstatus,

"Extent1".idultusuario AS idultusuario,

"Extent1".dtalteracao AS dtalteracao,

"Extent1".idempresa AS idempresa,

"Extent1".sigla AS sigla,

"Extent1".descricao AS descricao

FROM dbo.tblum AS "Extent1"
2011-11-07 23:14:54 BRT LOG: duração: 0.000 ms ligação PRSTMT213055026756562312/PORTAL213055026756562312: SELECT

"Extent1".idum AS idum,

"Extent1".idstatus AS idstatus,

"Extent1".idultusuario AS idultusuario,

"Extent1".dtalteracao AS dtalteracao,

"Extent1".idempresa AS idempresa,

"Extent1".sigla AS sigla,

"Extent1".descricao AS descricao

FROM dbo.tblum AS "Extent1"
2011-11-07 23:14:54 BRT LOG: executar PRSTMT213055026756562312/PORTAL213055026756562312: SELECT

"Extent1".idum AS idum,

"Extent1".idstatus AS idstatus,

"Extent1".idultusuario AS idultusuario,

"Extent1".dtalteracao AS dtalteracao,

"Extent1".idempresa AS idempresa,

"Extent1".sigla AS sigla,

"Extent1".descricao AS descricao

FROM dbo.tblum AS "Extent1"
2011-11-07 23:14:54 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:04 BRT LOG: duração: 0.000 ms análise de PRSTMT35568750256571874: INSERT INTO dbo.tblum(idstatus, idultusuario, dtalteracao, idempresa, sigla, descricao)

VALUES ($1, $2, $3, $4, $5, $6)

RETURNING idum
2011-11-07 23:15:04 BRT LOG: duração: 0.000 ms ligação PRSTMT35568750256571874/PORTAL35568750256571874: INSERT INTO dbo.tblum(idstatus, idultusuario, dtalteracao, idempresa, sigla, descricao)

VALUES ($1, $2, $3, $4, $5, $6)

RETURNING idum
2011-11-07 23:15:04 BRT DETALHE: parâmetros: $1 = '1', $2 = '1', $3 = '2011-11-08 00:14:54.055886', $4 = '1', $5 = 'TG', $6 = 'testegustavo'
2011-11-07 23:15:04 BRT LOG: executar PRSTMT35568750256571874/PORTAL35568750256571874: INSERT INTO dbo.tblum(idstatus, idultusuario, dtalteracao, idempresa, sigla, descricao)

VALUES ($1, $2, $3, $4, $5, $6)

RETURNING idum
2011-11-07 23:15:04 BRT DETALHE: parâmetros: $1 = '1', $2 = '1', $3 = '2011-11-08 00:14:54.055886', $4 = '1', $5 = 'TG', $6 = 'testegustavo'
2011-11-07 23:15:04 BRT LOG: duração: 20.000 ms
2011-11-07 23:15:15 BRT LOG: comando: SELECT version();
2011-11-07 23:15:15 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:15 BRT LOG: comando: SET DateStyle=ISO;
SET bytea_output=escape;
SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid
FROM pg_database WHERE oid = 64105
2011-11-07 23:15:15 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:15 BRT LOG: comando: set client_encoding to 'UNICODE'
2011-11-07 23:15:15 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: select * from dbo.tblum
2011-11-07 23:15:17 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:17 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:17 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 1114
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1114
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT format_type(oid,10) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1043
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT format_type(oid,54) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:17 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1043
2011-11-07 23:15:17 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:29 BRT LOG: duração: 0.000 ms análise de PRSTMT214061955056596741: SELECT

"Extent1".idum AS idum,

"Extent1".idstatus AS idstatus,

"Extent1".idultusuario AS idultusuario,

"Extent1".dtalteracao AS dtalteracao,

"Extent1".idempresa AS idempresa,

"Extent1".sigla AS sigla,

"Extent1".descricao AS descricao

FROM dbo.tblum AS "Extent1"
2011-11-07 23:15:29 BRT LOG: duração: 0.000 ms ligação PRSTMT214061955056596741/PORTAL214061955056596741: SELECT

"Extent1".idum AS idum,

"Extent1".idstatus AS idstatus,

"Extent1".idultusuario AS idultusuario,

"Extent1".dtalteracao AS dtalteracao,

"Extent1".idempresa AS idempresa,

"Extent1".sigla AS sigla,

"Extent1".descricao AS descricao

FROM dbo.tblum AS "Extent1"
2011-11-07 23:15:29 BRT LOG: executar PRSTMT214061955056596741/PORTAL214061955056596741: SELECT

"Extent1".idum AS idum,

"Extent1".idstatus AS idstatus,

"Extent1".idultusuario AS idultusuario,

"Extent1".dtalteracao AS dtalteracao,

"Extent1".idempresa AS idempresa,

"Extent1".sigla AS sigla,

"Extent1".descricao AS descricao

FROM dbo.tblum AS "Extent1"
2011-11-07 23:15:29 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: select * from dbo.tblum
2011-11-07 23:15:33 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:33 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 1114
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1114
2011-11-07 23:15:33 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT format_type(oid,10) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1043
2011-11-07 23:15:33 BRT LOG: duração: 10.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT format_type(oid,54) as typname FROM pg_type WHERE oid = 1043
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:33 BRT LOG: comando: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1043
2011-11-07 23:15:33 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:38 BRT LOG: duração: 0.000 ms análise de PRSTMT90733502656605992: COMMIT
2011-11-07 23:15:38 BRT LOG: duração: 0.000 ms ligação PRSTMT90733502656605992/PORTAL90733502656605992: COMMIT
2011-11-07 23:15:38 BRT LOG: executar PRSTMT90733502656605992/PORTAL90733502656605992: COMMIT
2011-11-07 23:15:38 BRT LOG: duração: 0.000 ms
2011-11-07 23:15:43 BRT LOG: comando: select * from dbo.tblum



* Both test codes worked fine in MS SQL SERVER

Can this be fixed ?

Thanks
folhamatic
 
Posts: 2
Joined: Tue 08 Nov 2011 01:57

Postby AlexP » Tue 08 Nov 2011 08:52

Hello,

Unfortunately, I cannot use the piece of code you've sent to reproduce the problem. Please send a sample application demonstrating the problem to alexp*devart*com for us to reproduce and fix it.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Postby wjchen119 » Tue 15 Nov 2011 02:50

Hello,
atd:ttransactiondesc;
scConnection: TSQLConnection;
sqGet: TSQLQuery;

sqGet.Close;
sqGet.SQL.Clear;
sqGet.SQL.Add('Begin Transaction');
sqGet.ExecSQL(false);

Insert Update Some Data
can't see change data,

If use
scConnection.StartTransaction(atd);

Insert Update Some Data
Data is correct
wjchen119
 
Posts: 4
Joined: Fri 04 Nov 2011 05:07

Postby AlexP » Tue 15 Nov 2011 08:46

Hello,

I still cannot reproduce the problem – in both cases after modifying/deleting/inserting data these changes are visible locally. That's why please send me a full sample demonstrating the problem to alexp*devart*com and also specify the version of your PostgreSQL server and dbExpress driver.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Postby folhamatic » Tue 15 Nov 2011 23:01

Hi,

i have sent you a sample, have you recived ?

Thanks
folhamatic
 
Posts: 2
Joined: Tue 08 Nov 2011 01:57

Postby wjchen119 » Wed 16 Nov 2011 01:14

Hello,I have send my sample.
wjchen119
 
Posts: 4
Joined: Fri 04 Nov 2011 05:07

Postby wjchen119 » Wed 16 Nov 2011 01:34

Hello,
My Environment:
OS:Windows XP
Delphi XE2

if i use Delphi 7 compile this Sample,
using TSQLQuery Data is correct.
wjchen119
 
Posts: 4
Joined: Fri 04 Nov 2011 05:07

Postby AlexP » Wed 16 Nov 2011 10:01

Hello,

I received your sample and reproduced the situation you described, but this behaviour is not a bug, because if the FetchAll property is set to False, we start a new transaction to get data if transaction was not opened before. I.e. if you start a transaction using the StartTransaction method explicitly, a new transaction for receiving data is not started, but if you start transaction using 'Begin Transaction', we don't know if transaction is started already and start a new one.
To change this behaviour, set the CursorWithHold or FetchAll property to true.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for PostgreSQL